pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCascade updates wait until the end of the transaction?
2+ messages / 2 participants
[nested] [flat]
* Cascade updates wait until the end of the transaction?
@ 2004-11-12 03:15 Thomas Berg <tomfuture@hotmail.com>
2004-11-16 06:52 ` Re: Cascade updates wait until the end of the transaction? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Thomas Berg @ 2004-11-12 03:15 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Cascade updates wait until the end of the transaction?
2004-11-12 03:15 Cascade updates wait until the end of the transaction? Thomas Berg <tomfuture@hotmail.com>
@ 2004-11-16 06:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2004-11-16 06:52 UTC (permalink / raw)
To: Thomas Berg <tomfuture@hotmail.com>; +Cc: pgsql-sql
On Fri, 12 Nov 2004, Thomas Berg wrote:
> 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
8.0 runs immediate triggers after each statement of a function and also
forces referential actions to be treated as immediate and as such should
change the behavior mentioned.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-11-16 06:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-12 03:15 Cascade updates wait until the end of the transaction? Thomas Berg <tomfuture@hotmail.com>
2004-11-16 06:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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