Re: 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 <ashaw(at)commandprompt(dot)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(at)commandprompt(dot)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(at)commandprompt(dot)com