[GENERAL] Locks acquired by "update" statement within serializable transaction.

[Pavel Suderevsky]

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.