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.