Re: How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) - Mailing list pgsql-sql

Transaction 1 operated on set of data (`WHERE` clause) on which 2nd transaction do an `INSERT`, which fit to clause from 1st transaction.
Shouldn't 1st transaction fail if 2nd commit first?

I have following table (in PostgreSQL 9.5 db)

`CREATE TABLE public.foo (id serial PRIMARY KEY, mynum integer);`

and following data

     id | mynum
    ----+-------
      1 |    10
      2 |    10
      3 |    10
      4 |    10
    (4 rows)

I run 2 serialize transactions in parallel (2 `psql` consoles):

    -- both transactions
    mydb=# begin;
    BEGIN
    mydb=# set transaction isolation level serializable;
    SET
   
    -- tx1
    mydb=# select * from foo where mynum < 100;
    id | mynum
    ----+-------
      1 |    10
      2 |    10
      3 |    10
      4 |    10
    (4 rows)
    --tx1: Shouldn't freeze data visible for tx1 select?
   
        --tx2
        mydb=# insert into foo (mynum) values (10);
        INSERT 0 1
        -- tx2 will insert next row with id 5 in foo table
        -- Shouldn't insert of tx2 broke data snapshot visible for tx1?
   
    --tx1
    mydb=# update foo set mynum = 20 where id < 100;
    UPDATE 4
    -- Shouldn't here appear serialization fail or at least on tx1 commit?
   
        --tx2
        mydb=# commit;
        COMMIT
   
    --tx1
    mydb=# commit;
    COMMIT
    -- tx1 Commit is OK - no any error
   
    -- implicit tx
    mydb=# select * from foo;
    id | mynum
    ----+-------
      1 |    20
      2 |    20
      3 |    20
      4 |    20
      5 |    10
    (4 rows)

I am wondering why it behave so, taking in consideration PostgreSQL documentation

> "To guarantee true serializability PostgreSQL uses predicate locking,
> which means that it keeps locks which allow it to determine when a
> write would have had an impact on the result of a previous read from a
> concurrent transaction, had it run first."
link: http://www.postgresql.org/docs/current/static/transaction-iso.html