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 that we're not doing what the SQL3 suggests
should be done (catch the foreign key errors before changes are made
and without incurring the cost of a rollback).
The current way we're doing it - identically to "NO ACTION" is
fine for compatability purposes, though later we'd like to implement
a smart ON DELETE RESTRICT because the efficiency considerations
that led to its inclusion in SQL3 are reasonable ones.
> 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.
- 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.