Skip site navigation (1) Skip section navigation (2)

Sequence increased before constraint check

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Sequence increased before constraint check
Date: 2001-02-14 18:13:58
Message-ID: 200102141813.f1EIDwm63627@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
David Lynn (davidl(at)ayamba(dot)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


Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-02-14 18:21:02
Subject: Explain reports unexpected results with inheritance
Previous:From: Stephan SzaboDate: 2001-02-14 00:35:02
Subject: Re: alter table rename and ruminations on referential integrity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group