Обсуждение: 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/