Re: Fw: postgresql experts please help

On Thu, 18 Oct 2007, Andrei Ilitchev wrote:

> IMO this behaviour (if there's existing sequence create another one with the
> same name but in some other place) is very wrong - be predictible, throw
> exception.

This is not true, but it can be confusing.  A serial's sequence will
always be put in the same schema as the table it's attached to.  If
another sequence exists with the same name in that schema, it will choose
another name for the serial sequence:

jurka=# create sequence t2_a_seq;
CREATE SEQUENCE
jurka=# create table t2 (a serial);
NOTICE:  CREATE TABLE will create implicit sequence "t2_a_seq1" for serial
column "t2.a"
CREATE TABLE


Notice that it created "t2_a_seq1" to avoid the conflict with the existing
"t2_a_seq" sequence.  If you've got another sequence with the same name in
another schema it does not conflict, but depending on your search_path you
can get them mixed up.  Both of these problems are solved by using
pg_get_serial_sequence, which should perhaps be mentioned more prominently
in the documentation.  There is definitely a use for keeping multiple
tables/sequences with identical names in different schemas and not having
them conflict.  In fact that's the primary use case for having schemas at
all.

Kris Jurka