pgsql: Add the MODE option to the WAIT FOR LSN command

Add the MODE option to the WAIT FOR LSN command

This commit extends the WAIT FOR LSN command with an optional MODE option in
the WITH clause that specifies which LSN type to wait for:

WAIT FOR LSN '<lsn>' [WITH (MODE '<mode>', ...)]

where mode can be:
- 'standby_replay' (default): Wait for WAL to be replayed to the specified
LSN,
- 'standby_write': Wait for WAL to be written (received) to the specified
LSN,
- 'standby_flush': Wait for WAL to be flushed to disk at the specified LSN,
- 'primary_flush': Wait for WAL to be flushed to disk on the primary server.

The default mode is 'standby_replay', matching the original behavior when MODE
is not specified. This follows the pattern used by COPY and EXPLAIN
commands, where options are specified as string values in the WITH clause.

Modes are explicitly named to distinguish between primary and standby
operations:
- Standby modes ('standby_replay', 'standby_write', 'standby_flush') can only
be used during recovery (on a standby server),
- Primary mode ('primary_flush') can only be used on a primary server.

The 'standby_write' and 'standby_flush' modes are useful for scenarios where
applications need to ensure WAL has been received or persisted on the standby
without necessarily waiting for replay to complete. The 'primary_flush' mode
allows waiting for WAL to be flushed on the primary server.

This commit also includes includes:
- Documentation updates for the new syntax and mode descriptions,
- Test coverage for all four modes, including error cases and concurrent
waiters,
- Wakeup logic in walreceiver for standby write/flush waiters,
- Wakeup logic in WAL writer for primary flush waiters.

Discussion: https://postgr.es/m/CABPTF7UiArgW-sXj9CNwRzUhYOQrevLzkYcgBydmX5oDes1sjg%40mail.gmail.com
Author: Xuneng Zhou <xunengzhou(at)gmail(dot)com>
Reviewed-by: Alexander Korotkov <aekorotkov(at)gmail(dot)com>
Reviewed-by: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
Reviewed-by: Alvaro Herrera <alvherre(at)kurilemu(dot)de>

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/49a181b5d634340fcfb7c762c387c03f6405367e

Modified Files
--------------
doc/src/sgml/ref/wait_for.sgml | 213 +++++++++++++----
src/backend/access/transam/xlog.c | 22 +-
src/backend/commands/wait.c | 174 ++++++++++++--
src/backend/replication/walreceiver.c | 18 ++
src/test/recovery/t/049_wait_for_lsn.pl | 411 +++++++++++++++++++++++++++++---
5 files changed, 741 insertions(+), 97 deletions(-)