Re: [HACKERS] Re: ALTER TABLE DROP COLUMN

Don Baccus wrote:

> At 01:43 AM 2/29/00 +0100, Jan Wieck wrote:
>
> >    ALL  the  FK  triggers  are  delayed  until  after the entire
> >    statement (what's wrong for ON DELETE RESTRICT -  but  that's
> >    another  story), or until the entire transaction (in deferred
> >    mode).
>
> Kind of wrong, just so folks understand the semantics are right in
> the sense that the right answer is given (pass or fail) - you need
> a stopwatch to know ...

    Explanative  version  of "that other story".  But not exactly
    correct IMHO. If following strictly SQL3 suggestions,  an  ON
    DELETE  RESTRICT  action cannot be deferrable at all. Even if
    the constraint itself is deferrable and is set explicitly  to
    DEFERRED,  the check should be done immediately at ROW level.
    That's the difference between "NO ACTION" and "RESTRICT".

    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!

> >    I'm far too less familiar with our implementation  of  nbtree
> >    to  tell  whether it would be possible at all to delay unique
> >    checking until statement end  or  XACT  commit.  At  least  I
> >    assume  it  would  require some similar technique of deferred
> >    queue.
>
> Presumably you'd queue up per-row triggers just like for FK constraints
> and insert into the unique index at that point.
>
> I have no idea how many other things this would break, if any.

    At least if deferring the index insert until XACT commit, any
    subsequent  index  scan wouldn't see inserted tuples, even if
    they MUST be visible.

    Maybe I'm less far away from knowledge than thought.   Inside
    of  a  nbtree-index,  any  number  of duplicates is accepted.
    It's the heap tuples visibility they point to, that  triggers
    the dup message.

    So  it's  definitely  some kind of "accept duplicates for now
    but check for final dup's on this key later".

    But that requires another index scan later. We  can  remember
    the  relations  and indices Oid (to get back the relation and
    index   in   question)   plus   the   CTID   of   the   added
    (inserted/updated   tuple)   to   get  back  the  key  values
    (remembering the key itself could blow up memory). Then do an
    index   scan   under   current  (statement  end/XACT  commit)
    visibility to check if more than one HeapTupleSatisfies().

    It'll be expensive, compared to current UNIQUE implementation
    doing  it  on  the fly during btree insert (doesn't it?). But
    the only way I see.


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) #