gnustep-dev
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Patch for gdl2 to handle LIMIT PostgreSQL keyword in PostgreSQLAdapt


From: Thierry DELHAISE
Subject: Re: Patch for gdl2 to handle LIMIT PostgreSQL keyword in PostgreSQLAdaptor.
Date: Mon, 16 Mar 2009 20:09:40 +0100
User-agent: Thunderbird 2.0.0.19 (X11/20090105)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi David,

David Ayers a écrit :
> Thanks a lot Thierry,
>
> Am Sonntag, den 15.03.2009, 22:27 +0100 schrieb Thierry DELHAISE:
>
>> Please find enclosed a patch that implement/use the PosgreSQL "LIMIT"
>> keyword in PostgreSQLAdaptor.
>
> I'm not yet convinced that we want to add this.  The
> EOFetchSpecification documents fetch limit to /either/ stop fetching
> objects when the limit is reached /or/ prompt the user to continue
> fetching.
>
> Now most database LIMIT implementation I know will merely return a
> result until limit is reached and have no way of "continuing" retrieval.
> What we do often have (and this includes PostgreSQL) is OFFSET to act
> like we are continuing in a subsequent query, but any inserts/deletes or
> updates to the sort order attributes could falsify this result.
>
> OTOH, I assume that for large result sets, this could be a nasty issue
> if the database needs to prepare the full result set. (GDL2 should only
> be requesting the data up to the fetch limit though.  If that's not
> happening please file a bug.)
>
> So are you requesting this for sake of performance?
> or because of "correctness" due to SQL-statement analysis?
In fact I can explain why I proposed this patch with an example.

Two daemons (based on gdl2) access the same database/table/schema and
those two daemons use a "shared" table (like a queue). Those two use
Pessimistic locking.

Those start their jobs by requesting some rows to update (FOR UPDATE)
with some criterias. But based on criterias, the select statement
execute on table can return more than one row and since FOR UPDATE is
used, the result is that all rows are locked. Since this is done in a
transaction, imagine one update failed, and you've to rollback all
records. So, the currently used/admit technic is to fetch 1 by 1 and
so lock FOR UPDATE only one row (this is where the "limit 1" is used.
This was what I wanted to do with gdl2 when I submit this patch. So
concurrent daemon continue to work and lock only one row at a time.
Sometimes, one daemon will be blocked until the other unlock the row.
But here , it is a fonctionnal problem (not the gdl2 one) : the select
algorythm use/share by all the daemon have to be well design to fit
this feature and so rely on "application implementation", not gdl2. So
here LIMIT keyword is used with FOR UPDATE and need to be manage by
database server, not by gdl2.

I admit that I didn't drive my reflection with OFFSET keyword in
mind... Since your mail I've investigated this way to try to identify
some side effect about my patch.

Hope this clarify my intentions.

Let me know.

Best regard.

Thierry
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkm+o88ACgkQe80ldkiJNIbWGQCfbESoC7mhI76NsjDQzwde1i/l
2TcAn1X7DrdZSUyfHua6DA+LEQWc8Vce
=mEQv
-----END PGP SIGNATURE-----





reply via email to

[Prev in Thread] Current Thread [Next in Thread]