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

[michael at diaspora.gen.nz]

>(c) In the perl you can make it "select, select, insert" as:
> 1. select the record (and fail).
> 2. select the sequence nextval
> 3. insert the row using the sequence.

There is of course a race condition here; between the select record
and select the sequence nextval, another thread could perform the full
process.  So be prepared to handle failures on insert if your sequence
is not the primary key, or potentially deal with duplicate rows, or wrap
a reliable transaction of some sort around the block.

(Personally, I'd do an INSERT ... RETURNING to give me back the nextval,
in the belief that minimizing database round trips is a good idea.)

DB2 has I believe a primitive for "INSERT OR UPDATE" which goes some way
to solving this problem; MySQL has INSERT ... IGNORE and INSERT ... ON
DUPLICATE KEY UPDATE, which may do the trick also.

    -- michael.