Thread

INITIALLY DEFERRED / UPDATE in transaction bug

Robert B. Easter <reaster@comptechnews.com> — 2000-07-09T13:01:54Z

============================================================================
                        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