BUG #19047: pg_last_wal_replay_lsn() returns non-NULL after pg_promote()

The following bug has been logged on the website:

Bug reference: 19047
Logged by: Mahadevan Ramachandran
Email address: mahadevan(at)rapidloop(dot)com
PostgreSQL version: 17.6
Operating system: Debian 12, amd64
Description:

After promoting a standby to a primary using pg_promote(), it is expected
that the server is not a primary and does not / cannot replay WALs, and
therefore pg_last_wal_replay_lsn() should return NULL. However, it does not.

To reproduce, setup a primary and a standby, and run the following commands
on the standby:

$ psql -h /tmp -p 8001 postgres
Null display is "~".
psql (17.6 (Debian 17.6-1.pgdg12+1))
Type "help" for help.

postgres=# select pg_last_wal_replay_lsn ();
pg_last_wal_replay_lsn
------------------------
0/40434E0
(1 row)

postgres=# select pg_promote();
pg_promote
------------
t
(1 row)

postgres=# select pg_last_wal_replay_lsn ();
pg_last_wal_replay_lsn
------------------------
0/40434E0
(1 row)

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

At this point, the server is a primary, but still reports
pg_last_wal_replay_lsn() as non-NULL.

We're reporting this from a real customer issue on the field.

Best,
-Mahadevan
(pgDash - https://pgdash.io)