collision in serial numbers after INSERT?

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.

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

On May 31, 2007, at 11:46 , lawpoop@gmail.com wrote:

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?

No.

http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.3

Michael Glaesemann
grzm seespotcode net

Hi;

Thanks Aurynn, but then I have another question --

Even if I do a 'SELECT nextval('your_sequence');', how do I prevent
an insert from happening between me selecting the next serial value
and then actually inserting it?

It seems like I should lock the table if i want to be certain.

SELECT nextval('your_sequence') updates the sequence as well, so the
next transaction that calls SELECT nextval('your_sequence') will get
your_return_value + 1. Once you SELECT nextval('your_sequence'), no
other call to nextval will get the sequence number you were just
given, barring an act such as using setval().

The logic would be akin to:

SELECT nextval('your_sequence');

-- any amount of stuff can happen here, including other transactions
that alter the sequence

INSERT INTO your_table (serial_field, data) VALUES
(sequence_value_you_selected, 'some data');

You can read more about how sequences work
http://www.postgresql.org/docs/current/static/functions-sequence.html

Hope that helps,
Aurynn.

On 6/1/07, Aurynn Shaw <ashaw@commandprompt.com > wrote:

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?

Everything that deals with sequences happens outside of transactions,
so this could theoretically happen.

The usual way to avoid this is to do:

SELECT nextval('your_sequence');

Then do your insert with that in the serial field.

Hope that helps,
Aurynn Shaw

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

ashaw@commandprompt.com

--
"Computers are useless. They can only give you answers"
-- Pablo Picasso

Aurynn Shaw

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

ashaw@commandprompt.com

Michael Glaesemann <grzm@seespotcode.net> writes:

On May 31, 2007, at 11:46 , lawpoop@gmail.com wrote:

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?

No.

Uh, yes, because he was using "SELECT last_value FROM seq" which is
indeed subject to race conditions. currval() would be far safer.

regards, tom lane

On Jun 1, 2007, at 15:00 , Tom Lane wrote:

Michael Glaesemann <grzm@seespotcode.net> writes:

On May 31, 2007, at 11:46 , lawpoop@gmail.com wrote:

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?

No.

Uh, yes, because he was using "SELECT last_value FROM seq" which is
indeed subject to race conditions. currval() would be far safer.

I read that much too quickly. My apologies, lawpoop. And thanks, Tom.

Michael Glaesemann
grzm seespotcode net

On 31 May 07 09:46:47 -0700, lawpoop@gmail.com <lawpoop@gmail.com> wrote:

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:

Another option is INSERT...RETURNING if you can alter your code. In
my environment it means lying to the system and telling it you are
doing a select when the SQL is actually an insert, but it all works.
If the insert fails, an error is returned, if it succeeds, the
values(s) you asked to have returned are in the result set.

- Ian

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:

Bill Moran wrote:

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

Hello Bill -

The 'offending' site and article is at
http://www.sitepoint.com/article/site-mysql-postgresql-2/3

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

I've also gotten other advice to SELECT next_val ( whatever the exact
wording is) will reserve that serial number for you. Is that true?

So l

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

Thanks!

Steve Lefevre <lefevre.10@osu.edu> wrote:

Bill Moran wrote:

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

Hello Bill -

The 'offending' site and article is at
http://www.sitepoint.com/article/site-mysql-postgresql-2/3

My goodness, that article is ancient. 2001. I have a hard time
believing he's going to update it if it's been wrong that long.

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

I've also gotten other advice to SELECT next_val ( whatever the exact
wording is) will reserve that serial number for you. Is that true?

Yes, please see the documentation. Both currval() and next_val() are
transaction safe (thus guaranteed not to cause overlapped serials) but
they do slightly different things.

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