Sequence increased before constraint check

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
checkfails, 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