Re: [GENERAL] collision in serial numbers after INSERT?
Steve Lefevre <[EMAIL PROTECTED]> 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(''), 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] collision in serial numbers after INSERT?
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(''), 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!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] 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 <[EMAIL PROTECTED]>
wrote:
In response to [EMAIL PROTECTED]:
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(''), which is guaranteed to be
isolated
from other sessions.
If you use the code above, sooner or later you're going to get bit.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] collision in serial numbers after INSERT?
On 31 May 07 09:46:47 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] collision in serial numbers after INSERT?
On Jun 1, 2007, at 15:00 , Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: On May 31, 2007, at 11:46 , [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] collision in serial numbers after INSERT?
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On May 31, 2007, at 11:46 , [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] collision in serial numbers after INSERT?
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 <[EMAIL PROTECTED] > 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
[EMAIL PROTECTED]
--
"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
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] collision in serial numbers after INSERT?
On May 31, 2007, at 11:46 , [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] collision in serial numbers after INSERT?
In response to [EMAIL PROTECTED]:
> 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(''), 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] 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.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
