Re: [HACKERS] Re: ALTER TABLE DROP COLUMN - Mailing list pgsql-hackers
From | Don Baccus |
---|---|
Subject | Re: [HACKERS] Re: ALTER TABLE DROP COLUMN |
Date | |
Msg-id | 3.0.1.32.20000229065110.01d02830@mail.pacifier.com Whole thread Raw |
In response to | Re: [HACKERS] Re: ALTER TABLE DROP COLUMN (wieck@debis.com (Jan Wieck)) |
List | pgsql-hackers |
At 11:22 AM 2/29/00 +0100, Jan Wieck wrote: >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. Ahhh...but the point you're missing, which was brought up a few days ago, is that this PG-ism of rejecting all further queries until COMMIT/ROLLBACK is in itself NONSTANDARD. As far as the effect of DEFERRED on RESTRICT with STANDARD, not PG, transaction semantics I've not investigated it. Neither one of us has a particularly great record at correctly interpreting the SQL3 standard regarding the subtleties of foreign key semantics, since we both had differing interpretations of RESTRICT/NO ACTION and (harumph) we were BOTH wrong :) Date implies that there's no difference other than RESTRICT's returning an error more quickly, but he doesn't talk about the DEFERRED case. Anyway, it's moot at the moment since neither RESTRICT nor standard SQL92 transaction semantics are implemented. > The end result will be the same, Which is what I mean when I say you pretty much need a stopwatch to tell the difference - OK, in PG you can look at the non-standard error messages due to the non-standard rejection of subsequent queries, but I was thinking in terms of standard transaction semantics. > 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. Yes. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
pgsql-hackers by date: