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@xxxxxxxxxxxxxxxxx > 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@xxxxxxxxxxxxxxxxx
--
"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@xxxxxxxxxxxxxxxxx
