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)
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.
