============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Robert B. Easter
Your email address : reaster@comptechnews.com
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Celeron
Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.13 Slackware
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0) : egcs-2.91.66
Please enter a FULL description of your problem:
------------------------------------------------
Referential integrity problem.
When using an INITIALLY DEFERRED foreign key within a transaction,
I give it a value that is not in the referenced table. Then I UPDATE
it so that it has a value in the referenced table. Then I COMMIT.
I still get an RI error on COMMIT.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
CREATE TABLE t1 (
t2_id INTEGER NOT NULL
);
CREATE TABLE t2 (
id SERIAL PRIMARY KEY
);
ALTER TABLE t1 ADD CONSTRAINT t1_t2_id_fk FOREIGN KEY (t2_id) REFERENCES t2 INITIALLY DEFERRED;
BEGIN;
INSERT INTO t1 VALUES (0);
INSERT INTO t2 VALUES (1);
SELECT t2_id FROM t1;
SELECT id FROM t2;
UPDATE t1 SET t2_id = 1 WHERE t2_id = 0;
SELECT t2_id FROM t1;
SELECT id FROM t2;
COMMIT; -- error
-- Or
CREATE TABLE t3 (
id SERIAL PRIMARY KEY
);
CREATE TABLE t4 (
t3_id INTEGER REFERENCES t3 INITIALLY DEFERRED
);
BEGIN;
INSERT INTO t4 VALUES (0);
INSERT INTO t3 VALUES (1);
SELECT t3_id FROM t4;
SELECT id FROM t3;
UPDATE t4 SET t3_id = 1 WHERE t3_id = 0;
SELECT t3_id FROM t4;
SELECT id FROM t3;
COMMIT; -- again, error
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
--
Robert
