Use of Serial Datatype and Sequence Issue

Tom Innes <tinnes@inforamp.net> writes:

I am new to postgres and am in the process of creating a new database of
several 100 tables. Most of the tables have a serial datatype. When the
table is created psql says it is creating an implicit sequence. Yet
when I drop the table and even when I drop the database the sequence is
still there. When I try to re-create the table it fails beacuse the
sequence already exists. This same behaviour exists for indexes. How
can I get rid of any sequences or indexes that have been created. I
would prefer not to explicitly drop each one. Ideally I would like to
drop the table and have all related indexes, sequences dropped.

Hmmm, I see part of this bug in 7.1.2 as well. The index goes away
when the table is dropped, but the sequence remains and causes an
error when I try to recreate the table. But dropping the database
(with DROP DATABASE or with 'destroydb' from the shell) blows away
everything, including the sequence, as it should.

Haven't got 7.1.3 to try it on--what version are you using?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

The message may be a bit misleading. using a SERIAL data type actually means
that you are implicitly creating a sequence. A sequence is a separate
object, not tied to any column. Data type SERIAL is just shorthand for
defining an INT4 column and a sequence object with a DEFAULT clause on the
column calling the sequence. You can do this explicitly as well. The docs
may not be clear about this relationship. Hence, you have to drop the
sequences explicitly if you don't want them anymore.

IMHO, when you do a data-only pg_dump as SQL inserts, the fact that SQL to
create sequences is dumped as well seems a bug to me. Any plans to change
this, Tom Lane?

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

On Lun 05 Nov 2001 17:03, you wrote:

The message may be a bit misleading. using a SERIAL data type actually
means that you are implicitly creating a sequence. A sequence is a separate
object, not tied to any column. Data type SERIAL is just shorthand for
defining an INT4 column and a sequence object with a DEFAULT clause on the
column calling the sequence. You can do this explicitly as well. The docs
may not be clear about this relationship. Hence, you have to drop the
sequences explicitly if you don't want them anymore.

IMHO, when you do a data-only pg_dump as SQL inserts, the fact that SQL to
create sequences is dumped as well seems a bug to me. Any plans to change
this, Tom Lane?

Where's the bug? The inserts come with the value inserted into the INT column
(origanally SERIAL), so all that has to be taken care of is making the
sequence start where it is, which is the behaviour of pg_dump.

What would be great is a binary dump, with it's reload application, so that
BIG backups/restores could be done in a small amount of time.

Saludos... :-)

--
Porqu� usar una base de datos relacional cualquiera,
si pod�s usar PostgreSQL?
-----------------------------------------------------------------
Mart�n Marqu�s | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------

Where's the bug? The inserts come with the value inserted into the INT column
(origanally SERIAL), so all that has to be taken care of is making the
sequence start where it is, which is the behaviour of pg_dump.

The bug is that CREATE staements are issued as well as SELECT nextval()
which readies the sequence. When restoring from a data only dump, since the
sequences are created by the schema (especially when using SERIAL data
types), the sequence creation statements will fail because the sequences
already exist, and since the initial value of the sequence is set in the
CREATE statement, which fails, the sequence will end up reset (back to 1)
and your sequencing is off. If sequences are used as unique identifiers
(which they often are), you are in for a big surprise with numerous
exceptions on inserts. The data only dump with proper inserts should output
SELECT setval() statements instead of CREATE SEQUENCE statements. IMHO ;-)

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

At 00:28 02/11/01 +0000, you wrote:

I am new to postgres and am in the process of creating a new database of
several 100 tables. Most of the tables have a serial datatype. When the
table is created psql says it is creating an implicit sequence. Yet
when I drop the table and even when I drop the database the sequence is
still there. When I try to re-create the table it fails beacuse the
sequence already exists. This same behaviour exists for indexes. How
can I get rid of any sequences or indexes that have been created. I
would prefer not to explicitly drop each one. Ideally I would like to
drop the table and have all related indexes, sequences dropped.

pgAdmin2 knows how to drop indexes when dropping a table:
http://pgadmin.postgresql.org
It might not be interesting to drop a sequence as it can be used by
multiple tables.

Dave: do you think pgAdmin2 should drop a sequence when dropping a table?

Best regards,
Jean-Michel POURE

Keary Suska <hierophant@pcisys.net> writes:

The bug is that CREATE staements are issued as well as SELECT nextval()
which readies the sequence.

What version are you running? I see no such behavior in either 7.1.3 or
current sources: pg_dump -a produces only

SELECT setval ('"foo_f2_seq"', 2, 't');

No sign of a CREATE SEQUENCE.

regards, tom lane

Whoops--my bad--that was an issue with 7.0.3. I don't see that in my last
dump with 7.1.3. Thanks!

On a separate issue, in 7.0.3, the \connect lines would fail because the
passwords for the root user and the database owner are different, so I would
have to dump and reload as root user, otherwise the pg_class updates would
fail. Has this been solved in 7.1.3? I notice that the dump switches between
users depending on the action required. Does this require activating pg_dump
as root user, and doesn't require the database owner password, or are each
prompted when needed?

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

Keary Suska <hierophant@pcisys.net> writes:

On a separate issue, in 7.0.3, the \connect lines would fail because the
passwords for the root user and the database owner are different, so I would
have to dump and reload as root user, otherwise the pg_class updates would
fail. Has this been solved in 7.1.3?

I believe that 7.1 psql will prompt for passwords as necessary, so this
works, but it's awfully tedious. Better to arrange things so that you
don't need to give a password.

There is support in 7.2 (not sure if it was in 7.1) for the ownership
changes to be executed via "SET current_userid" style commands, so that
as long as you connect as superuser to start with (to be allowed to do
this SET) you shouldn't need to answer password prompts for individual
ownership changes. But in a pg_dumpall script you'll probably still
have to answer once per database ...

regards, tom lane