Thread: BUG #1127: ALTER SEQUENCE bug

The following bug has been logged online:

Bug reference:      1127
Logged by:          Piotr Konieczny

Email address:      znahor@vix.netsync.pl

PostgreSQL version: 7.4

Operating system:   Linux

Description:        ALTER SEQUENCE bug

Details:

ALTER SEQUENCE doesn't work properly, when only first value has been readed
from sequence:

test=# create TABLE t (s serial, t text);
NOTICE:  CREATE TABLE will create implicit sequence "t_s_seq" for "serial"
column "t.s"
NOTICE:  CREATE TABLE will create implicit sequence "t_s_seq" for "serial"
column "t.s"
CREATE TABLE
test=# insert into t (t) VALUES ('1');
INSERT 17151 1
test=# select * from t;
 s | t
---+---
 1 | 1
(1 row)

test=# delete from t;
DELETE 1

test=# ALTER sequence t_s_seq restart 1;
ALTER SEQUENCE
test=# insert into t (t) VALUES ('1');
INSERT 17152 1
test=# select * from t;
 s | t
---+---
 2 | 1
(1 row)

test=# delete from t;
DELETE 1
test=# ALTER sequence t_s_seq restart 1;
ALTER SEQUENCE
test=# insert into t (t) VALUES ('1');
INSERT 17153 1
test=# select * from t;
 s | t
---+---
 1 | 1
(1 row)

test=#

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> ALTER SEQUENCE doesn't work properly, when only first value has been readed
> from sequence:

Yup, you're right; this was sloppily coded.  I've applied the attached
patch.

            regards, tom lane

Index: sequence.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/sequence.c,v
retrieving revision 1.103.2.1
diff -c -r1.103.2.1 sequence.c
*** sequence.c    24 Nov 2003 16:54:15 -0000    1.103.2.1
--- sequence.c    6 Apr 2004 16:28:03 -0000
***************
*** 315,346 ****
      seq = read_info(elm, seqrel, &buf);
      page = BufferGetPage(buf);

!     /* copy old values of options */
!     new.increment_by = seq->increment_by;
!     new.max_value = seq->max_value;
!     new.min_value = seq->min_value;
!     new.cache_value = seq->cache_value;
!     new.is_cycled = seq->is_cycled;
!     new.last_value = seq->last_value;

      /* Check and set new values */
      init_params(stmt->options, &new, false);

      /* Now okay to update the on-disk tuple */
!     seq->increment_by = new.increment_by;
!     seq->max_value = new.max_value;
!     seq->min_value = new.min_value;
!     seq->cache_value = new.cache_value;
!     seq->is_cycled = new.is_cycled;
!     if (seq->last_value != new.last_value)
!     {
!         seq->last_value = new.last_value;
!         seq->is_called = false;
!         seq->log_cnt = 1;
!     }

!     /* save info in local cache */
!     elm->last = new.last_value; /* last returned number */
      elm->cached = new.last_value;        /* last cached number (forget
                                           * cached values) */

--- 315,331 ----
      seq = read_info(elm, seqrel, &buf);
      page = BufferGetPage(buf);

!     /* Copy old values of options into workspace */
!     memcpy(&new, seq, sizeof(FormData_pg_sequence));

      /* Check and set new values */
      init_params(stmt->options, &new, false);

      /* Now okay to update the on-disk tuple */
!     memcpy(seq, &new, sizeof(FormData_pg_sequence));

!     /* Clear local cache so that we don't think we have cached numbers */
!     elm->last = new.last_value;            /* last returned number */
      elm->cached = new.last_value;        /* last cached number (forget
                                           * cached values) */

***************
*** 1008,1020 ****
--- 993,1011 ----

      /* START WITH */
      if (last_value != (DefElem *) NULL)
+     {
          new->last_value = defGetInt64(last_value);
+         new->is_called = false;
+         new->log_cnt = 1;
+     }
      else if (isInit)
      {
          if (new->increment_by > 0)
              new->last_value = new->min_value;    /* ascending seq */
          else
              new->last_value = new->max_value;    /* descending seq */
+         new->is_called = false;
+         new->log_cnt = 1;
      }

      /* crosscheck */