Re: BUG #15875: Unexpected serializable isolation error when INSERTing into a table

My thanks for such a speedy reply and your analysis. This sounds like it
would be really useful for preventing 'unnecessary' retries. Is there a
page/mailing list/group for feature requests where I or we could formalise
and track it?

Thanks,

Henri

On Wed, 26 Jun 2019, 11:34 Thomas Munro, <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Wed, Jun 26, 2019 at 10:05 PM PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> > -- Transaction 1:
> > ```
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > SELECT id FROM foo WHERE id = 44;
>
> For me, this acquires an SIReadLock on the page because it's an index
> only scan. Then ...
>
> > -- Transaction 2:
> > ```
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > SELECT id FROM foo WHERE id = 33;
>
> ... this does the same, on the same page, so the transactions form a
> cycle when they write. You can see the page locks in the pg_locks
> view. In other words, you can get bogus serialisation failures when
> you access nearby keys. It seems like we could probably fix this in a
> future release by switching to tuple locks (or rather TID locks) for
> index only scans, as briefly discussed a while ago but not yet
> followed up:
>
>
> https://www.postgresql.org/message-id/CAEepm%3D2QbqQ_%2BKQQCnhKukF6NEAeq4SqiO3Qxe%2BfHza5-H-jKA%40mail.gmail.com
>
> --
> Thomas Munro
> https://enterprisedb.com
>