Re: WIP: Detecting SSI conflicts before reporting constraint violations

On Sun, Jan 31, 2016 at 5:19 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> 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?

I don't feel qualified to have an opinion on whether this is an
improvement. I'm a little skeptical of changes like this on general
principle because sometimes one clientele wants error A to be reported
rather than error B and some other clientele wants the reverse.
Deciding who is right is above my pay grade.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company