Thread: Sequences question & problem
Hi there, I am having small trouble with sequences. I am inserting row into table, and I need to get value of its 'id' column (sequencen type). As far I know it have to be done by SELECT last_value FORM seq_name (or next_val before insert). Normally, sequences are created by 'table' & 'column' & '_seq' eg. 'table_id_seq'. Now let's rename 'table' to 'table2'. Sequence name wan't renamed. And it's my problem... I can't simple assume 'sequence id' by table/column... I see 2 solution: 1. udpate pg_class after renaming. Something like: UPDATE pg_class SET relname='table2_id_seq' WHERE relname='table_id_seq' Will this work well for all possible cases ? Is this good way ? I tried that that and then 'insert' itselfs generates error: ERROR: Relation "public"."table_id_seq" does not exist It seems it was looking for OLD sequence name. I used psql for updating, while postgres was normally running. Is this problem on my side or I can't simple change 'relname' of pg_class ? 2. make code reading 'sequence names' live. I looked around pg_class but I am not sure which relation between reltype,relfilenode,reltoastrelid are proper ones... So, how to get 'sequence name' by its 'table name' ? Name of sequence and table are not related (we can't assume some prefix in name, because table was renamed) Will this pg_class reference cost many CPU ? Thank you, Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -------------------------------------------------------------------------- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. Imagination is more important than knowledge...
Thank you.. I didn't know about race condition.. Shame on me :) > If you're using PostgreSQL 8.0 or later then you can get the sequence > name with the pg_get_serial_sequence() function: Is there any way how to do that (or add this function) in 7.3.x ? Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -------------------------------------------------------------------------- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. Imagination is more important than knowledge...
On Mon, May 16, 2005 at 12:29:27PM +0200, Lada 'Ray' Lostak wrote: > > I am having small trouble with sequences. I am inserting row into table, > and I need to get value of its 'id' column (sequencen type). As far I know > it have to be done by > > SELECT last_value FORM seq_name > > (or next_val before insert). The above queries are subject to race conditions. For example, if transaction A inserts a record, then transaction B inserts a record, then transaction A queries the sequence, then transaction A will get the sequence value that was given to transaction B, which probably isn't what you want. Use currval() or nextval() instead. http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html http://www.postgresql.org/docs/faqs.FAQ.html#4.11.2 http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3 > Normally, sequences are created by 'table' & 'column' & '_seq' eg. > 'table_id_seq'. > > Now let's rename 'table' to 'table2'. Sequence name wan't renamed. And it's > my problem... I can't simple assume 'sequence id' by table/column... If you're using PostgreSQL 8.0 or later then you can get the sequence name with the pg_get_serial_sequence() function: test=> CREATE TABLE foo (id serial); NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" CREATE TABLE test=> ALTER TABLE foo RENAME TO foo2; ALTER TABLE test=> ALTER TABLE foo2 RENAME id TO id2; ALTER TABLE test=> SELECT pg_get_serial_sequence('foo2', 'id2'); pg_get_serial_sequence ------------------------ public.foo_id_seq (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/