Re: Use of Serial Datatype and Sequence Issue

> 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 
> Date: Mon, 5 Nov 2001 20:14:54 -0300
> To: Keary Suska 
> Cc: 
> 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
>