Edward Grabczewski ([EMAIL PROTECTED]) 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
