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.27157@wrigleys.postgresql.org
--
Thomas Munro
http://www.enterprisedb.com
