Re: [HACKERS] Re: ALTER TABLE DROP COLUMN - Mailing list pgsql-hackers

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
deferrableand is set explicitly  to   DEFERRED,  the check should be done immediately at ROW level.   That's the
differencebetween "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
codeof 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
relationand   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) #