Re: Transactional DDL, but not Serializable

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> Well, basically, you can't have that. Example: you have an existing
>> table with primary key, and while you're in the middle of doing some
>> long transaction, somebody else creates a table with a foreign-key
>> reference to the one you're about to do a delete from. Being
>> serializable does not excuse you from the obligation to check for
>> FK violations in that "invisible" table. It might be acceptable to
>> fail entirely, but not to act as though the table isn't there.

> That's an excellent example and point. Is there a 'right' answer (with
> regard to the SQL spec, what other databases do, etc)?

I'm not aware that anybody's got an amazingly satisfactory solution.
PG's answer is of course to use up-to-the-minute DDL regardless of what
the transaction might see for other purposes, which certainly has got
disadvantages if you're hoping for truly serializable behavior. But I'm
not sure there's a better answer. You could make an argument for
failing any serializable transaction that's affected by DDL changes that
happen after it started. I don't know whether that cure would be worse
than the disease.

regards, tom lane