From: "Thomas Berg" <tomfuture@hotmail.com>
Subject: Cascade updates wait until the end of the transaction?
Date: Fri, 12 Nov 2004 12:15:21 +0900
Organization: Hub.Org Networking Services
Lines: 46
Message-ID: <cn19tg$1gq5$2@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-2022-jp";
	reply-type=original
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org

Within a function (and therefore within a transaction), if I UPDATE the
primary key of a table, the old value remains visible in the child table
through the end of the function.  Is this
a) by design, and a good thing for some reason I don't understand
b) by design, but should be changed
c) a bug in 7.4.3 that has been fixed in version XXX
d) an unfixed bug
e) a heretofore unnoticed bug?
f) something else?

And has anyone any recommendations how to get around this?  The only thing I
can think of is to make 2 separate calls from the client, which I would be
very sad to do.

This demonstrates what I'm talking about.  Run each block and check the
output before proceeding to the next block (don't run line by line).

Thanks for any insight.
Berg

-- BLOCK 1
CREATE TABLE t1 (
  t1_code char(3) NOT NULL PRIMARY KEY
);
CREATE TABLE t2 (
  t1_code char(3) REFERENCES t1 (t1_code) ON UPDATE CASCADE ON DELETE SET
NULL,
  t2_name varchar
);
INSERT INTO t1 (t1_code) VALUES ('ABC');
INSERT INTO t2 (t1_code, t2_name) VALUES ('ABC', 'I refer to ABC');

UPDATE t1 SET t1_code = 'DEF' WHERE t1_code = 'ABC';
UPDATE t2 SET t2_name = 'I refer to ' || t1_code;
SELECT * FROM t2;
-- shows ABC | I refer to ABC

-- BLOCK 2
SELECT * FROM t2;
-- shows DEF | I refere to ABC

-- BLOCK 3 : clean up
DROP TABLE t2;
DROP TABLE t1;