Thread

1. Sequence increased before constraint check

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-14T18:13:58Z

   David Lynn (davidl@ayamba.com) reports a bug with a severity of 3
   The lower the number the more severe it is.
   
   Short Description
   Sequence increased before constraint check
   
   Long Description
   When a table is created with a serial column and another column that has a foreign key constraint, if the foreign key check fails, the sequence will still be updated.  This results in potential gaps in the serial column.
   
   Sample Code
   DEV:menu# create table tab1 (col1 serial primary key);
   NOTICE:  CREATE TABLE will create implicit sequence 'tab1_col1_seq' for SERIAL c
   olumn 'tab1.col1'
   NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'tab1_pkey' for tab
   le 'tab1'
   CREATE
   DEV:menu# create table tab2 (col1 serial, col2 int4 references tab1);
   NOTICE:  CREATE TABLE will create implicit sequence 'tab2_col1_seq' for SERIAL c
   olumn 'tab2.col1'
   NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'tab2_col1_key' for tabl
   e 'tab2'
   NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
   CREATE
   DEV:menu# insert into tab2 (col2) values (null);
   INSERT 28971 1
   DEV:menu# select * from tab2;
    col1 | col2
   ------+------
       1 |
   (1 row)
   
   DEV:menu# insert into tab2 (col2) values (666);
   ERROR:  <unnamed> referential integrity violation - key referenced from tab2 not
    found in tab1
   DEV:menu# insert into tab2 (col2) values (null);
   INSERT 28973 1
   DEV:menu# select * from tab2;
    col1 | col2
   ------+------
       1 |
       3 |
   (2 rows)
   
   
   No file was uploaded with this report
   
   
   

2. Re: Sequence increased before constraint check

   Peter Eisentraut <peter_e@gmx.net> — 2001-02-14T18:46:43Z

   > When a table is created with a serial column and another column that
   > has a foreign key constraint, if the foreign key check fails, the
   > sequence will still be updated.  This results in potential gaps in the
   > serial column.
   
   This is not a bug.  If you cannot have gaps in your number sequence then
   you cannot use serial.
   
   -- 
   Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/