Re: [HACKERS] Re: ALTER TABLE DROP COLUMN

At 03:24 AM 2/29/00 +0100, Jan Wieck wrote:

>    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!

No, strictly speaking it isn't correct.  But without a stopwatch,
it will be hard to tell.

Actually, though, since exceptions are only supposed to reject
the given SQL-statement and not trigger a PG-style auto-rollback of
the transaction, a subsequent "commit" should commit that subsequent
work (unless they in turn trigger constraint errors due to dependencies
on the first failed constraint).

So you don't really get to skip all those subsequent statements unless
you're looking for the exception, catch it, and do an explicit rollback.

None of that is in place in PG anyway at the moment...

I'm assuming that the exception raised for an FK violation is the
same as an exception raised for numeric overflow, etc - I think 
you missed that earlier discussion.

The fact that PG's auto-rollback is wrong was news to me, though
obvious in hindsight, and I've not gone back to study RI semantics
in light of this new information.

So I may be wrong, here.  

We could always take out "RESTRICT" and claim SQL92 rather than SQL3
referential integrity :) :)

Given that Oracle only implements "MATCH <unspecified>" (as of 8.1.5,
anyway), we're not doing too bad!

>
>> >    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.

Ugh, of course :(

>    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.

The more I learn about SQL92 the more I understand why RDBMS systems
have the reputation for being piggy.  But, the standard semantics
of UPDATE on a column with a UNIQUE constraint are certainly consistent
with the paradigm that queries operate on sets of tuples, not sequences
of tuples.



- 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.