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(at)debis(dot)com (Jan Wieck) #