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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.