Thread: collision in serial numbers after INSERT?
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?
I don't think this would be a problem in our environment, but I am
just wondering.
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:
> 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 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