Re: Unexpected serialization error

Thank you, Andres. I appreciate your thoughts. So, I do notice the lock
level changes from table to page as soon as there are various rows in the
table. But what's really astonishing to me is to start looking at pages as
predicate granularity and, consequently, the possibility of the same test
passing with certain amount of data in the tables. This is a great find for
me.

On Mon, 22 Feb 2021, 02:28 Andres Freund, <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> On 2021-02-12 19:14:17 +0100, Luka Žitnik wrote:
> > I had no luck over at general slack channel. So I'm beginning to treat
> this
> > as a bug. Here's a test case that unexpectedly fails at last insert. I
> > expect it not to fail because the rows that the two transactions act on
> are
> > unrelated to one another.
> >
> > CREATE TABLE t1 (
> > class integer NOT NULL
> > );
> >
> > CREATE INDEX ON t1 (class);
> >
> > CREATE TABLE t2 (
> > class integer NOT NULL
> > );
> >
> > CREATE INDEX ON t2 (class);
> >
> > ALTER SYSTEM SET enable_seqscan TO off;
> >
> > -- Session 1
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > SELECT * FROM t2 WHERE class=1;
> >
> > -- Session 2
> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> > SELECT * FROM t1 WHERE class=2;
> > INSERT INTO t2 VALUES(2);
> > COMMIT;
> >
> > -- Session 1
> > INSERT INTO t1 VALUES(1);
> > COMMIT;
>
> I think you're hitting multiple issues here... For one, initially your
> table is empty, in which case we mark the entire index to be a conflict
> during for the SELECT * FROM t2 WHERE class=1;
>
> Second, even if the indexes weren't empty, the granularity of the index
> predicate locking is a page - which means that S1's predicate lock for
> SELECT * FROM t1 WHERE class=2; will conflict with S1's INSERT INTO t1
> VALUES(1).
>
> If the SELECTs actual hit rows (and the inserts were updates), you'd
> presumably have the sequence suceed. Same with an index where the
> inserted values weren't on the same page as the looked up values.
>
> Greetings,
>
> Andres Freund
>