Thread: Use of Serial Datatype and Sequence Issue
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. Tom
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" > From: Tom Innes <tinnes@inforamp.net> > Organization: Excite@Home - The Leader in Broadband http://home.com/faster > Date: Fri, 02 Nov 2001 00:28:18 GMT > To: pgsql-general@postgresql.org > Subject: [GENERAL] Use of Serial Datatype and Sequence Issue > > 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. > > Tom > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
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" > From: Martín Marqués <martin@bugs.unl.edu.ar> > Date: Mon, 5 Nov 2001 20:14:54 -0300 > To: Keary Suska <hierophant@pcisys.net> > Cc: <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] Use of Serial Datatype and Sequence Issue > > 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 > ----------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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
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
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"
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Tue, 06 Nov 2001 13:54:09 -0500
> To: Keary Suska <hierophant@pcisys.net>
> Cc: Martín Marqués <martin@bugs.unl.edu.ar>, pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Use of Serial Datatype and Sequence Issue
>
> 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
>