Bug #518: SERIAL type value not seen in FOREIGN KEY

Edward Grabczewski (edward(dot)grabczewski(at)btinternet(dot)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 SERIAL column '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 in arch_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 in arch_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