Re: collision in serial numbers after INSERT?
От | Aurynn Shaw |
---|---|
Тема | Re: collision in serial numbers after INSERT? |
Дата | |
Msg-id | 21AB862A-BD98-4989-8AF1-E17971D154AD@commandprompt.com обсуждение исходный текст |
Ответ на | collision in serial numbers after INSERT? (lawpoop@gmail.com) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: