Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Mon, Dec 12, 2016 at 1:06 PM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
> On Mon, Dec 12, 2016 at 12:32 PM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
>
>> As you can see, this generated a serialization failure.
>
> That was on 9.6. On earlier versions it does indeed allow the
> transaction on connection 2 to commit, yielding a non-serializable
> result. This makes a pretty strong case for back-patching this
> commit:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fcff8a575198478023ada8a48e13b50f70054766

I have confirmed that this patch applies cleanly to all supported
branches (with some offsets), that the bug is manifest without the
patch, and that it is fixed with this patch in all supported
branches. This patch has been in the development code base for
about 8 months and in production with the 9.6 release, so it has
been in active production for 3 months with no sign of trouble. If
you ignore the code comment, doc changes, and new regression tests
it consists of adding one line of code to nbtinsert.c. What it
does is that when a "unique_violation" error is about to fire, it
adds a check to see whether the conditions for a serialization
failure also exist; if so, it fires that error instead.

This was not initially back-patched, in spite of numerous requests
to do so, because it was a behavior change and not clearly a bug --
it has a least some minimal chance of changing behavior someone
might be relying on; however, Ian has constructed a use-case where
without this patch we clearly allow a serialization anomaly which
is not allowed with the patch, so this patch should, IMO, be
considered a bug fix on that basis.

Barring objections I will back-patch to 9.2 through 9.5 tomorrow.
(9.1 is out of support and the fix is already in 9.6 and forward.)

Release notes for the next minor release set should probably
include a note that for those using serializable transactions a
serialization error can now occur in some situations where a unique
violation would previously have been reported. I expect that this
will mostly be a welcome change, but it seems like something which
merits a compatibility warning.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company