Re: collision in serial numbers after INSERT?

In response to lawpoop(at)gmail(dot)com:

> Hello all -
>
> I'm working on a site with PHP and Postgres, coming from a MySQL
> background.
>
> I was looking for an equivalent to the mysql_insert_id() function, and
> a site recommended this:
>
> function postg_insert_id($tablename, $fieldname)
> {
> global connection_id;
> $result=pg_exec($connection_id, "SELECT last_value FROM ${tablename}_
> ${fieldname}_seq");
> $seq_array=pg_fetch_row($result, 0);
> return $seq_array[0];
> }
>
> It relies on pg's sequencing ability.
>
> However, I wondered, if I were in an environment where there were many
> concurrent inserts, would it be possible that I didn't get the serial
> number of the insert that *I* just did? That if I do an insert, and
> several inserts happen after mine, wouldn't I get the id of the latest
> row, which is several inserts after mine?

Don't do that. Please let us know what site recommended that so I can
send an email to the author correcting them.

Instead, do SELECT currval('<seqname>'), which is guaranteed to be isolated
from other sessions.

If you use the code above, sooner or later you're going to get bit.

--
Bill Moran
http://www.potentialtech.com