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@debis.com (Jan Wieck) #