Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> * Tom Lane (tgl@sss.pgh.pa.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.
If transaction A commits successfully before transaction B commits, regardless
of when transaction B started, and transaction A changes/adds/etc any
constraints on the database, then I would expect transaction B to only commit
successfully if all of its data changes pass those new/changed constraints.
If B were allowed to commit without that being the case, then it would leave the
database in an inconsistent state, that is a state where its data doesn't
conform to its constraints. A database should always be consistent on
transaction boundaries, at the very least, if not on statement boundaries.
As to whether B's failure happens when it tries to commit or happens earlier,
based on visibility issues with A's changes, doesn't matter to me so much (do
what works best for you/others), but it should fail at some point if it would
otherwise cause inconsistencies.
-- Darren Duncan