An example of bugs for Hot Standby

Hot Standby node can freeze when startup process calls LockBufferForCleanup().
This bug can be reproduced by the following procedure.

0. start Hot Standby, with one active node(node A) and one standby node(node B)
1. create table X and table Y in node A
2. insert several rows in table X in node A
3. delete one row from table X in node A
4. begin xact 1 in node A, execute following commands, and leave xact 1 open
4.1 LOCK table Y IN ACCESS EXCLUSIVE MODE
5. wait until WAL's for above actions are applied in node B
6. begin xact 2 in node B, and execute following commands
6.1 DECLARE CURSOR test_cursor FOR SELECT * FROM table X;
6.2 FETCH test_cursor;
6.3 SELECT * FROM table Y;
7. execute VACUUM FREEZE table A in node A
8. commit xact 1 in node A

...then in node B occurs following "deadlock" situation, which is not detected by deadlock check.
* startup process waits for xact 2 to release buffers in table X (in LockBufferForCleanup())
* xact 2 waits for startup process to release ACCESS EXCLUSIVE lock in table Y

This situation can occur when
a) a transaction in the standby node tries to acquire ACCESS SHARE lock while holding some buffers
b) startup process calls LockBufferForCleanup() for any of the buffers

regards,

--
Hiroyuki YAMADA
Kokolink Corporation
yamada(at)kokolink(dot)net