Re: [HACKERS] Re: ALTER TABLE DROP COLUMN

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 WHERE a = 3;
COMMIT TRANSACTION;

In this case, the first DELETE from t1 must already bomb the
exception, setting the transaction block into error state and
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(at)debis(dot)com (Jan Wieck) #