Re: Transactional DDL, but not Serializable

* 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)? When you go to
delete a record from the existing table you could get a FK violation due
to the invisible table, which could end up being rolled back and
removed.

It seems like the semantics around this would call for the adding-FK
transaction to be treated as if the table did already exist and then
handle this case as we would if there wasn't any DDL involved. Another
approach might be to wait till commit to check the FK, but that'd
probably be considered unkind.

If the spec doesn't dictate anything and/or we can't find anyone else's
semantics that make sense, I suppose we'll need to define our own. To
that end, perhaps we should put up something on a wiki or similar to
start capturing these and considering what the 'right' answer would be.

Apologies for my ignorance on this.

Thanks,

Stephen