Locks on updating rows with foreign key constraint

Question

I tried executing the same UPDATE query twice like below.

First time the transaction has no lock but I can see a row lock after second query.

Schema:

test=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 i      | integer |           | not null | 
 j      | integer |           |          | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (i)
Referenced by:
    TABLE "t2" CONSTRAINT "t2_j_fkey" FOREIGN KEY (j) REFERENCES t1(i)

test=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 i      | integer |           | not null | 
 j      | integer |           |          | 
 k      | integer |           |          | 
Indexes:
    "t2_pkey" PRIMARY KEY, btree (i)
Foreign-key constraints:
    "t2_j_fkey" FOREIGN KEY (j) REFERENCES t1(i)

Existing data:

test=# SELECT * FROM t1 ORDER BY i;
 i | j 
---+---
 1 | 1
 2 | 2
(2 rows)

test=# SELECT * FROM t2 ORDER BY i;
 i | j | k 
---+---+---
 3 | 1 |  
 4 | 2 |  
(2 rows)

UPDATE queries and row lock status:

test=# BEGIN;
BEGIN
test=# UPDATE t2 SET k = 123 WHERE i = 3;
UPDATE 1
test=# SELECT * FROM t1 AS t, pgrowlocks('t1') AS p WHERE p.locked_row = t.ctid;
 i | j | locked_row | locker | multi | xids | modes | pids 
---+---+------------+--------+-------+------+-------+------
(0 rows)

test=# UPDATE t2 SET k = 123 WHERE i = 3;
UPDATE 1
test=# SELECT * FROM t1 AS t, pgrowlocks('t1') AS p WHERE p.locked_row = t.ctid;
 i | j | locked_row | locker | multi |   xids   |       modes       | pids 
---+---+------------+--------+-------+----------+-------------------+------
 1 | 1 | (0,1)      | 107239 | f     | {107239} | {"For Key Share"} | {76}
(1 row)

test=# 

Why does postgres try to get a row lock only on second time?

By the way, queries updating column t2.j create new lock (ForKeyShare) on t1 row at once. This behavior make sense because t2.j has foreign key constraint references t1.i. But the queries above seems not.

Does anyone can explain this lock?

PostgreSQL version: 9.6.3

Answer 1

Okay, I got it.

http://blog.nordeus.com/dev-ops/postgresql-locking-revealed.htm

This is optimization that exists in Postgres. If locking manager can figure out from the first query that foreign key is not changed (it is not mentioned in update query or is set to same value) it will not lock parent table. But in second query it will behave as it is described in documentation (it will lock parent table in ROW SHARE locking mode and referenced row in FOR SHARE mode)

It seems MySQL is wiser about foreign key locks because the same UPDATE query doesn't make such locks on MySQL.