Re: Review: Hot standby

<br />ISTM that the redo conflict resolution is not working as intended. I did the following test and it throws some
surprises.<br/><br />On standby:<br /><br />postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;<br />BEGIN<br
/>postgres=# SELECT * from test;<br />  a  | b<br />-----+---<br /> 102 |<br /> 103 |<br />(2 rows)<br /><br /><br />On
primary:<br/><br />postgres=# SELECT * from test;<br />  a  | b<br />-----+---<br /> 102 |<br /> 103 |<br />(2 rows)<br
/><br/>postgres=#<br /> postgres=# UPDATE test SET a = a + 100;<br />UPDATE 2<br />postgres=# VACUUM test;<br
/>VACUUM<br/>postgres=# SELECT pg_switch_xlog();<br /> pg_switch_xlog<br />----------------<br /> 0/2D000288<br />(1
row)<br/><br /><br />On standby (server log):<br /><br />LOG:  restored log file "00000001000000000000002D" from
archive<br/>LOG:  recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks
(currentdelay now 5 secs)<br />CONTEXT:  xlog redo exclusive relation lock: slot 99 db 11517 rel 24576<br /> LOG: 
recoverycancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5
secs)<br/>CONTEXT:  xlog redo exclusive relation lock: slot 99 db 11517 rel 24576<br />LOG:  recovery cancels activity
ofvirtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs)<br /> CONTEXT:  xlog
redoexclusive relation lock: slot 99 db 11517 rel 24576<br />LOG:  recovery cancels activity of virtual transaction 2/2
pid10593 because it blocks exclusive locks (current delay now 5 secs)<br />CONTEXT:  xlog redo exclusive relation lock:
slot99 db 11517 rel 24576<br /> LOG:  recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks
exclusivelocks (current delay now 5 secs)<br />CONTEXT:  xlog redo exclusive relation lock: slot 99 db 11517 rel
24576<br/>LOG:  recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks
(currentdelay now 5 secs)<br /> CONTEXT:  xlog redo exclusive relation lock: slot 99 db 11517 rel 24576<br />LOG: 
recoverycancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5
secs)<br/>CONTEXT:  xlog redo exclusive relation lock: slot 99 db 11517 rel 24576<br /> <same message
repeated><br/><br /><br />The open transaction (see above) on the standby is not still not aborted and if I query
thetable in the same transaction, I get:<br /><br />(Note: the transaction is still open)<br />postgres=#<br />
postgres=#SELECT * from test;<br /> a | b<br />---+---<br />(0 rows)<br /><br /><br />I think whats happening is that
ResolveRecoveryConflictWithVirtualXIDs()is failing to abort the open transaction and it keeps trying for that,
everytimedoubling the sleep time, so the LOG messages come less frequently later, but they are never ending. Soon the
sleepbecomes exponentially large.<br /><br />Even though the standby has a open transaction, its obvious that the
cleanup_redohas also failed to abort the  transaction. Thats why the tuples have disappeared from the standby (most
likelybecause they are cleaned up by VACUUM).<br /><br /><br />Thanks,<br />Pavan<br /><br />-- <br />Pavan Deolasee<br
/>EnterpriseDB    <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br />