Don Baccus wrote:
> At 03:24 AM 2/29/00 +0100, Jan Wieck wrote:
>
> > Actually, a RESTRICT violation can potentially bypass
> > thousands of subsequent queries until COMMIT. Meaningless
> > from the transactional PoV, but from the application
> > programmers one (looking at the return code of a particular
> > statement) it isn't!
>
> No, strictly speaking it isn't correct. But without a stopwatch,
> it will be hard to tell.
It is easy to tell:
CREATE TABLE t1 (a integer PRIMARY KEY); CREATE TABLE t2 (a integer REFERENCES t1
ON DELETE RESTRICT DEFERRABLE);
INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (2);
BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED; DELETE FROM t1 WHERE a = 2; DELETE FROM t1
WHEREa = 3; COMMIT TRANSACTION;
In this case, the first DELETE from t1 must already bomb the exception, setting the transaction block into error
stateand reject all further queries until COMMIT/ROLLBACK. The SET DEFERRED should only affect a check for
key existance on INSERT to t2, not the RESTRICT action on DELETE to t1.
The end result will be the same, both DELETEs get rolled back. But the application will see it at COMMIT, not
at the first DELETE. So the system behaves exactly like for NO ACTION.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #