Bug #518: SERIAL type value not seen in FOREIGN KEY - Mailing list pgsql-bugs

Edward Grabczewski (edward.grabczewski@btinternet.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
SERIAL type value not seen in  FOREIGN KEY

Long Description
I have defined a table called arch_object which contains a SERIAL type.
I have defined another table called mm_object which REFERENCES this SERIAL type column.
I insert a value into table arch_object. This generates the next value, which is seen using a SELECT statement.
I try to insert a value into mm_object based on this new value in table arch_object and the INSERT is rejected.
I cannot see why as this value plainly exists in the arch_object table and should be picked up.

SQL CODE and OUTPUT given below.

cheers
Eddy

Sample Code
============SCRIPT BEGIN: inherit.txt ===============================
DROP TABLE deposit;
DROP TABLE arch_object;
DROP SEQUENCE arch_object_arch_object_id_seq;

CREATE TABLE arch_object
(
   arch_object_id SERIAL  PRIMARY KEY,
   date_excavated TIMESTAMP
);

CREATE TABLE deposit
(
   deposit_id    INTEGER
)
INHERITS (arch_object);

INSERT INTO deposit(date_excavated,deposit_id)
VALUES (CURRENT_TIMESTAMP,300);
SELECT * from deposit;

DROP TABLE mm_object;
CREATE TABLE mm_object
(
   mm_object      OID,
   arch_object_id INTEGER REFERENCES arch_object(arch_object_id)
);

INSERT INTO arch_object(date_excavated) VALUES (CURRENT_TIMESTAMP);
SELECT * from arch_object;
INSERT INTO mm_object(arch_object_id)   VALUES (1);
SELECT * from mm_object;
INSERT INTO mm_object(arch_object_id)   VALUES (3);
SELECT * from mm_object;


INSERT INTO arch_object(date_excavated) VALUES (CURRENT_TIMESTAMP);
SELECT * from arch_object;
INSERT INTO mm_object(arch_object_id)   VALUES (2);
SELECT * from mm_object;
INSERT INTO mm_object(arch_object_id)   VALUES (3);
SELECT * from mm_object;

INSERT INTO arch_object(date_excavated) VALUES (CURRENT_TIMESTAMP);
SELECT * from arch_object;
INSERT INTO mm_object(arch_object_id)   VALUES (3);
SELECT * from mm_object;
INSERT INTO mm_object(arch_object_id)   VALUES (3);
SELECT * from mm_object;
============SCRIPT END: inherit.txt ===============================


============ SCREEN OUTPUT BEGIN ===============================
test=# \i /tmp/inherit02.txt


DROP TABLE deposit;
psql:/tmp/inherit02.txt:3: ERROR:  table "deposit" does not exist

DROP TABLE arch_object;
psql:/tmp/inherit02.txt:5: ERROR:  table "arch_object" does not exist
DROP SEQUENCE arch_object_arch_object_id_seq;
psql:/tmp/inherit02.txt:6: ERROR:  sequence "arch_object_arch_object_id_seq" does not exist


CREATE TABLE arch_object
(
   arch_object_id SERIAL  PRIMARY KEY,
   date_excavated TIMESTAMP
);
psql:/tmp/inherit02.txt:13: NOTICE:  CREATE TABLE will create implicit sequence 'arch_object_arch_object_id_seq' for
SERIALcolumn 'arch_object.arch_object_id' 
psql:/tmp/inherit02.txt:13: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'arch_object_pkey' for table
'arch_object'
CREATE


CREATE TABLE deposit
(
   deposit_id    INTEGER
)
INHERITS (arch_object);
CREATE

-- INSERT INTO deposit(arch_object_id,deposit_id)
-- VALUES (nextval('arch_object_arch_object_id_seq'),300);
-- SELECT * from deposit;

INSERT INTO deposit(date_excavated,deposit_id)
VALUES (CURRENT_TIMESTAMP,300);
INSERT 143965 1
SELECT * from deposit;
              1 | 2001-11-21 20:12:52+00 |        300


DROP TABLE mm_object;
psql:/tmp/inherit02.txt:30: ERROR:  table "mm_object" does not exist
CREATE TABLE mm_object
(
   mm_object      OID,
   arch_object_id INTEGER REFERENCES arch_object(arch_object_id)
);
psql:/tmp/inherit02.txt:35: NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE


INSERT INTO arch_object(date_excavated) VALUES (CURRENT_TIMESTAMP);
INSERT 143983 1
SELECT * from arch_object;
              2 | 2001-11-21 20:13:24+00
              1 | 2001-11-21 20:12:52+00

INSERT INTO mm_object(arch_object_id)   VALUES (1);
psql:/tmp/inherit02.txt:40: ERROR:  <unnamed> referential integrity violation - key referenced from mm_object not found
inarch_object 
SELECT * from mm_object;

INSERT INTO mm_object(arch_object_id)   VALUES (3);
psql:/tmp/inherit02.txt:42: ERROR:  <unnamed> referential integrity violation - key referenced from mm_object not found
inarch_object 
SELECT * from mm_object;



INSERT INTO arch_object(date_excavated) VALUES (CURRENT_TIMESTAMP);
INSERT 143986 1
SELECT * from arch_object;
              2 | 2001-11-21 20:13:24+00
              3 | 2001-11-21 20:13:30+00
              1 | 2001-11-21 20:12:52+00

INSERT INTO mm_object(arch_object_id)   VALUES (2);
INSERT 143987 1
SELECT * from mm_object;
           |              2

INSERT INTO mm_object(arch_object_id)   VALUES (3);
INSERT 143988 1
SELECT * from mm_object;
           |              2
           |              3


INSERT INTO arch_object(date_excavated) VALUES (CURRENT_TIMESTAMP);
INSERT 143989 1
SELECT * from arch_object;
              2 | 2001-11-21 20:13:24+00
              3 | 2001-11-21 20:13:30+00
              4 | 2001-11-21 20:13:35+00
              1 | 2001-11-21 20:12:52+00

INSERT INTO mm_object(arch_object_id)   VALUES (3);
INSERT 143990 1
SELECT * from mm_object;
           |              2
           |              3
           |              3

INSERT INTO mm_object(arch_object_id)   VALUES (3);
INSERT 143991 1
SELECT * from mm_object;
           |              2
           |              3
           |              3
           |              3


test=#
============ SCREEN OUTPUT END ===============================


No file was uploaded with this report