Re: Documenting when to retry on serialization failure

On Thu, 24 Mar 2022 at 14:05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Thomas Munro <thomas(dot)munro(at)gmail(dot)com> writes:
> > On Thu, Mar 24, 2022 at 11:44 PM Simon Riggs
> > <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> >> The unique violation thing is worryingly general. Do we know enough to
> >> say that this is thought to occur only with a) multiple unique
> >> constraints, b) exclusion constraints?
>
> > I'm aware of 3 cases. The two you mentioned, which I think we can fix
> > (as described in the threads I posted upthread), and then there is a
> > third case that I'm still confused about, in the last line of
> > read-write-unique-4.spec.
>
> That test is modeling the case where the application does an INSERT
> with values based on some data it read earlier. There is no way for
> the server to know that there's any connection, so I think if you
> try to throw a serialization error rather than a uniqueness error,
> you're basically lying to the client by claiming something you do not
> know to be true. And the lie is not without consequences: if the
> application believes it, it might iterate forever vainly trying to
> commit a transaction that will never succeed.

OK, I see what you mean. There are 2 types of transaction, one that
reads inside the transaction, one that decides what value to use some
other way.

So now we have 2 cases, both of which generate uniqueness violations,
but only one of which might succeed if retried. The patch does cover
this, I guess, by saying be careful, but I would be happier if we can
also add

"this is thought to occur only with multiple unique constraints and/or
an exclusion constraints"

--
Simon Riggs http://www.EnterpriseDB.com/