Hi,
I would ask for clarification about logic of locks acquired by update statements within serializable transactions.
Tried postgres 9.3.6 and postgres 9.4.4.
Story 1.
testdb=# \dS+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
s | integer | not null | plain | |
i | integer | | plain | |
Indexes:
"t_pkey" PRIMARY KEY, btree (s)
Has OIDs: no
testdb=# begin transaction isolation level serializable;
BEGIN
testdb=# update t set i=867 where s=2;
UPDATE 1
And this it what I've expected: SIReadLock + RowExclusiveLock:
testdb=# SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted
from pg_locks l, pg_stat_all_tables t
where l.relation=t.relid and t.relname = 't';
relname | locktype | page | virtualtransaction | pid | mode | granted
---------+----------+------+--------------------+-------+------------------+---------
t | relation | | 12/1000023 | 30865 | RowExclusiveLock | t
t | relation | | 12/1000023 | 30865 | SIReadLock | t
(2 rows)
Story 2.
testdb=# \d+ rollover
Table "public.rollover"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
n | integer | | plain | |
Indexes:
"rollover_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
testdb=# begin transaction isolation level serializable;
BEGIN
testdb=# update rollover set n = 5 where id = 2;
UPDATE 1
And this is what I didn't expect:
testdb=# SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted
from pg_locks l, pg_stat_all_tables t
where l.relation=t.relid and t.relname = 'rollover';
relname | locktype | page | virtualtransaction | pid | mode | granted
----------+----------+------+--------------------+-------+------------------+---------
rollover | relation | | 12/1000031 | 30865 | RowExclusiveLock | t
(1 row)
Why? How is it possible? I was expecting the similar SSI behaviour of this two similar stories.