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