Re: collision in serial numbers after INSERT?

    In the last versions of postgres, do :

    INSERT INTO blah RETURNING blah_id

    No need to worry about sequences or anything. It inserts, then it returns
the inserted id, as the name says.

    Very much unlike MySQL where insert_id() returns the id of the last
insert, even if it was done in an ON INSERT TRIGGER so isn't what you want
at all !



On Fri, 01 Jun 2007 21:39:49 +0200, Bill Moran <wmoran@potentialtech.com>
wrote:

> In response to lawpoop@gmail.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.
>