Thread: BUG #2290: Incorrect sequence increment after backup/restore

The following bug has been logged online:

Bug reference:      2290
Logged by:          Oleg Mamontov
Email address:      oleg@mamontov.net
PostgreSQL version: 8.0.x/8.1.x
Operating system:   FreeBSD 4.x,5.x,6.x
Description:        Incorrect sequence increment after backup/restore
Details:

If after CREATE TABLE with SERIAL column i'll change sequence increment to 2
or some other value (with ALTER SEQUENCE) then always work correctly (all
inserted rows will have values incremented by 2).
But after database backup/restore (with pg_dump) this ALTER will not
restored and all next inserted rows will have values incremented by 1
(default for SERIAL data type).
It's seems like a bug...
Changing increment value often used with replication (master database
inserts has odd values and slave database inserts has even) and other
situations.

Sorry for my poor English.

"Oleg Mamontov" <oleg@mamontov.net> writes:
> If after CREATE TABLE with SERIAL column i'll change sequence increment to 2
> or some other value (with ALTER SEQUENCE) then always work correctly (all
> inserted rows will have values incremented by 2).

We currently consider that a serial column is a "black box" and you
should not mess with its underlying sequence directly.  If you want
nondefault properties for the sequence, create it as an independent
sequence object and then just declare the column as "integer
default nextval('seq')".

            regards, tom lane

Tom Lane wrote:
> "Oleg Mamontov" <oleg@mamontov.net> writes:
> > If after CREATE TABLE with SERIAL column i'll change sequence increment to 2
> > or some other value (with ALTER SEQUENCE) then always work correctly (all
> > inserted rows will have values incremented by 2).
>
> We currently consider that a serial column is a "black box" and you
> should not mess with its underlying sequence directly.  If you want
> nondefault properties for the sequence, create it as an independent
> sequence object and then just declare the column as "integer
> default nextval('seq')".

Added to TODO:

    * %Disallow changing sequence characteristics like INCREMENT for
      SERIAL columns

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

Updated wording:

    * %Disallow ALTER SEQUENCE changes for SERIAL sequences because pg_dump
      does not dump the changes

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > "Oleg Mamontov" <oleg@mamontov.net> writes:
> > > If after CREATE TABLE with SERIAL column i'll change sequence increment to 2
> > > or some other value (with ALTER SEQUENCE) then always work correctly (all
> > > inserted rows will have values incremented by 2).
> >
> > We currently consider that a serial column is a "black box" and you
> > should not mess with its underlying sequence directly.  If you want
> > nondefault properties for the sequence, create it as an independent
> > sequence object and then just declare the column as "integer
> > default nextval('seq')".
>
> Added to TODO:
>
>     * %Disallow changing sequence characteristics like INCREMENT for
>       SERIAL columns
>
> --
>   Bruce Momjian   http://candle.pha.pa.us
>   SRA OSS, Inc.   http://www.sraoss.com
>
>   + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +