Hi hackers, As described in a recent Reddit discussion[1] and bug report 9301[2], there are scenarios where overlapping concurrent read-write sequences produce serialization failures without constraints, but produce constraint violations when there is a unique constraint. A simple example is deciding on a new value for a primary key by first checking the existing contents of a table.
This makes life difficult if you're trying to build systems that automatically retry SERIALIZABLE transactions where appropriate, because you have to decide how and when to handle unique constraint violations too. For example, people have experimented with automatic retry-on-40001 at the level of HTTP requests for Django applications when using the middleware that maps HTTP requests to database transactions, and the same opportunities presumably exist in Java application servers and other web service technologies, but unique constraint violations get in the way of that. Here is an experimental patch to report such SSI conflicts. I had to make a change to aminsert_function so that the snapshot could be available to btree insert code (which may be unpopular). The questions on my mind now are: Are there still conflicting schedules that would be missed, or significant new spurious conflict reports, or other theoretical soundness problems? Is that PredicateLockPage call sound and cheap enough? It is the only new code that isn't in a path already doomed to ereport, other than the extra snapshot propagation, and without it read-write-unique-3.spec (taken from bug report 9301) doesn't detect the conflict. Thoughts? [1] https://www.reddit.com/r/PostgreSQL/comments/3z74v2/postgres_acid_transactions_and_locking_to_prevent/ [2] http://www.postgresql.org/message-id/flat/20140221002001.29130.27...@wrigleys.postgresql.org -- Thomas Munro http://www.enterprisedb.com
ssi-read-write-unique-wip.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
