From: Thomas Berg <tomfuture@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Cascade updates wait until the end of the transaction?
Date: Fri, 12 Nov 2004 12:15:21 +0900
Message-ID: <cn19tg$1gq5$2@news.hub.org> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tomfuture@hotmail.com
Subject: Re: Cascade updates wait until the end of the transaction?
In-Reply-To: <cn19tg$1gq5$2@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox