Tom Lane wrote:
Stephen Frost <sfr...@snowman.net> writes:
* Tom Lane (t...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
