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.

---------------------------(end of broadcast)---------------------------
TIP 1: 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

"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

---------------------------(end of broadcast)---------------------------
TIP 1: 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

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

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. +

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match