Re: Unique constraint error instead of serialization_failure

On Thu, May 21, 2020 at 9:24 AM Benny Kramek <benny(at)medflyt(dot)com> wrote:
> Hello, I have found an example where I expect to get a serialization failure
error, but instead receive a unique constraint error. My understanding is that
this is not supposed to happen starting with PostgreSQL version 9.6 because of
this patch:
>
> <https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fcff8a575198478023ada8a48e13b50f70054766>

I'm also running into this bug (with both Postgres 13.7 and 15.1) and am
wondering if there's any progress on this bug, or if there is a known
workaround for it.

After explaining what I saw on IRC, "ysch" (Yaroslav Schekin) pointed
me to this wiki page: https://wiki.postgresql.org/wiki/SerializableToDo
and I was able to write a test case in my own code base to confirm this
is what I'm running into.

While fiddling with my tests, I noticed that when you REINDEX _either_
of the indexes in the testcase before starting the transactions, somehow
a serialization error is triggered instead of a uniqueness constraint
error!  I verified that this also happens with the minimal testcase
provided by OP.

Of course this is not exactly a useful workaround for a production db,
but maybe it helps in finding out why this is failing the way it is?

Cheers,
Peter Bex