[Wellington-pm] Perl database stuff....

[Srdjan]

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

Yes, been there. In those cases table invariably refers to some other_table with
business_key (if I'm corect) as the primary key. So what I do is:

BEGIN;
SELECT * FROM other_table WHERE business_key = 'blah';
$id = SELECT id FROM table WHERE whatever;
if ($row) {
  UPDATE table SET whatever WHERE id = $id;
} else {
  $id = SELECT nextval('sequence');
  INSERT...
}
COMMIT; (or ROLLBACK) at liking.

Srdjan

Ewen McNeill wrote:
> In message <E1Gz5jc-0004lS-00 at israel.diaspora.gen.nz>, michael at diaspora.gen.nz writes:
>>    A: select 1 from table where business_key = ...; # off to database
>>    if select returned nothing
>>    begin
>> 	B: select sequence.nextval into id; # off to database
>> 	C: insert into table values (id, business_key); # off to database
>>    end;
>>
>>    [Race between A and B/C without external locking]
> 
> Since we're talking PostgreSQL, you can turn B and C into one statement:
> 
> INSERT INTO table (id, business_key, ....)
> SELECT nextval('sequence'), 'business_key', 'other', 'parameters', ....
> 
> (and then use "SELECT currval('sequence');" to get the ID used -- if you
> only want it to use in another insert, you can do the same thing as
> above on the next INSERT or UPDATE, but with currval() on that sequence
> -- I do this quite frequently when preparing interrelated test data just
> with basic SQL statements).
> 
> But you still have the race between A and the combined B/C statement
> without some more locking.
> 
> Where it's possible to create a unique index on the business key, that's
> probably the most robust solution (then just try the insert, and check
> if it failed because of a duplicate business key, rather than selecting
> for it first).  It also avoids duplicates arrising in some other way
> (eg, initial data import).
> 
> Where that's not possible, it's more tricky to avoid the race, and hence
> the duplicates.  PostgreSQL supports "SELECT FOR UPDATE":
> 
> http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE
> 
> but that appears not to lock against inserts (presumably it relies on
> having an actual row to lock, rather than just a potential key; the 
> locking section of the PostgreSQL documentation seems to confirm that
> it's not locked against inserts since "SELECT FOR UPDATE" takes a less
> exclusive lock than "INSERT").
> 
> Another possibility would be to explicitly acquire the "access
> exclusive" table lock on the table in question:
> 
> http://www.postgresql.org/docs/8.1/static/explicit-locking.html
> 
> prior to doing the "is it there" select.  But that's (a) non-portable,
> and (b) potentially blocking the database for more time than is
> desirable.  (It also appears to need to be done in an explicit
> transaction as the way of releasing it.)
> 
> A third possibility seems to be:
> 
> INSERT INTO table (id, business_key, ....)
> SELECT nextval('sequence'), 'business_key', ....
> WHERE NOT EXISTS 
>       (SELECT 1 FROM table WHERE business_key='business_key' limit 1);
> 
> as suggested here:
> 
> http://beta.nntp.perl.org/group/perl.dbi.users/2006/08/msg29954.html
> 
> It appears to be valid PostgreSQL, and to work in the simple cases (ie,
> record doesn't exist, record exists, only one simultaneous statement),
> but I'm not yet convinced it doesn't still have a tiny race inside
> the database.  Creating a UNIQUE INDEX on the business key seems safer
> (and less involved!).
> 
> This discussion (Drupal, PHP) is possibly of interest too:
> 
> http://drupal.org/node/1811
> 
> They appear to have settled on taking a table lock and dealing with
> database variations in doing that.  (Presumably with PHP they're 
> already dealing with enough other database access variations that
> it doesn't matter.)
> 
> Ewen
> 
> PS: You could do locking in the application, but then you're effectively
>     doing advisory locking rather than mandatory locking, and you will
>     race against anything which doesn't play along with your advisory
>     locking.
> _______________________________________________
> Wellington-pm mailing list
> Wellington-pm at pm.org
> http://mail.pm.org/mailman/listinfo/wellington-pm
> 
> 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFkZzsZtcHxCitRpgRAsNxAKCpL1lqFzctKrHMzuzlPLj20hOREwCfcxcG
QCn8bDIr+7vQ8AEbaMvT13w=
=OTUD
-----END PGP SIGNATURE-----