Some problems of recovery conflict wait events

Hi all,

When recovery conflicts happen on the streaming replication standby,
the wait event of startup process is null when
max_standby_streaming_delay = 0 (to be exact, when the limit time
calculated by max_standby_streaming_delay is behind the last WAL data
receipt time is behind). Moreover the process title of waiting startup
process looks odd in the case of lock conflicts.

1. When max_standby_streaming_delay > 0 and the startup process
conflicts with a lock,

* wait event
backend_type | wait_event_type | wait_event
--------------+-----------------+------------
startup | Lock | relation
(1 row)

* ps
42513 ?? Ss 0:00.05 postgres: startup recovering
000000010000000000000003 waiting

Looks good.

2. When max_standby_streaming_delay > 0 and the startup process
conflicts with a snapshot,

* wait event
backend_type | wait_event_type | wait_event
--------------+-----------------+------------
startup | |
(1 row)

* ps
44299 ?? Ss 0:00.05 postgres: startup recovering
000000010000000000000003 waiting

wait_event_type and wait_event are null in spite of waiting for
conflict resolution.

3. When max_standby_streaming_delay > 0 and the startup process
conflicts with a lock,

* wait event
backend_type | wait_event_type | wait_event
--------------+-----------------+------------
startup | |
(1 row)

* ps
46510 ?? Ss 0:00.05 postgres: startup recovering
000000010000000000000003 waiting waiting

wait_event_type and wait_event are null and the process title is
wrong; "waiting" appears twice.

The cause of the first problem, wait_event_type and wait_event are not
set, is that WaitExceedsMaxStandbyDelay which is called by
ResolveRecoveryConflictWithVirtualXIDs waits for other transactions
using pg_usleep rather than WaitLatch. I think we can change it so
that it uses WaitLatch and those caller passes wait event information.

For the second problem, wrong process title, the cause is also
relevant with ResolveRecoveryConflictWithVirtualXIDs; in case of lock
conflicts we add "waiting" to the process title in WaitOnLock but we
add it again in ResolveRecoveryConflictWithVirtualXIDs. I think we can
have WaitOnLock not set process title in recovery case.

This problem exists on 12, 11 and 10. I'll submit the patch.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services