Re: Transaction isolation and table contraints

On Wed, Nov 25, 2020 at 8:14 AM Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> wrote:

> Hi hackers,
>
> I wonder if it is considered as correct behavior that transaction
> conflict detection depends on presence of primary key:
>
> create table t(pk integer, val integer);
> insert into t values (1,0),(2,0);
>
> ERROR: could not serialize access due to read/write dependencies among
> transactions
> [...]
> Now let's repeat the same scenario but with "pk" declared as primary key:
>
> create table t(pk integer primary key, val integer);
> insert into t values (1,0),(2,0);
>
> Now both transactions are succeeded.
>

From the docs:

"A sequential scan will always necessitate a relation-level predicate lock.
This can result in an increased rate of serialization failures."

The two seem possibly related (I'm not experienced with using serializable)

https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE

> Please notice, that even if it is expected behavior, hint in error
> message is not correct, because transaction is actually aborted and
> there is no chance to retry it.
>
>
It is technically correct, it just doesn't describe precisely how to
perform said retry, leaving that up to the reader to glean from the
documentation.

The hint assumes users of serializable isolation mode are familiar with
transaction mechanics. In particular, the application needs to be prepared
to retry failed transactions, and part of that is knowing that PostgreSQL
will not automatically rollback a failed transaction for you, it is
something that must be done as part of the error detection and recovery
infrastructure that is needed when writing applications that utilize
serializable.

David J.