Date: Wed, 25 Jan 2023 13:29:11 +0100
From: Peter Bex <peter.bex@bevuta.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: Unique constraint error instead of serialization_failure
Message-ID: <Y9Egl2e8STe9IiY7@mail.networkname.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAGPCyEZG76zjv7S31v_xPeLNRuzj-m=Y2GOY7PEzu7vhB=yQog@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Y9Egl2e8STe9IiY7%40mail.networkname.de>
Precedence: bulk

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