Horton
Menu

Re: Implement waiting for wal lsn replay: reloaded

Xuneng Zhou <xunengzhou@gmail.com>

View thread
From: Xuneng Zhou <xunengzhou@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>, Álvaro Herrera <alvherre@kurilemu.de>, Chao Li <li.evan.chao@gmail.com>, pgsql-hackers <pgsql-hackers@lists.postgresql.org>, Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>, jian he <jian.universality@gmail.com>, Tomas Vondra <tomas@vondra.me>, Yura Sokolov <y.sokolov@postgrespro.ru>
Date: 2026-01-06T17:04:06Z
Lists: pgsql-hackers

Attachments

Hi,

On Tue, Jan 6, 2026 at 11:58 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:
>
> Hi,
>
>
> On Tue, Jan 6, 2026 at 11:34 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > On Tue, Jan 6, 2026 at 3:12 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:
> > >
> > > Hi,
> > >
> > > On Tue, Jan 6, 2026 at 7:54 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > > >
> > > > On Tue, Jan 6, 2026 at 9:29 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:
> > > > > On Tue, Jan 6, 2026 at 1:43 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> > > > > > Could this be causing the recent flapping failures on CI/macOS in
> > > > > > recovery/031_recovery_conflict?  I didn't have time to dig personally
> > > > > > but f30848cb looks relevant:
> > > > > >
> > > > > > Waiting for replication conn standby's replay_lsn to pass 0/03467F58 on primary
> > > > > > error running SQL: 'psql:<stdin>:1: ERROR:  canceling statement due to
> > > > > > conflict with recovery
> > > > > > DETAIL:  User was or might have been using tablespace that must be dropped.'
> > > > > > while running 'psql --no-psqlrc --no-align --tuples-only --quiet
> > > > > > --dbname port=25195
> > > > > > host=/var/folders/g9/7rkt8rt1241bwwhd3_s8ndp40000gn/T/LqcCJnsueI
> > > > > > dbname='postgres' --file - --variable ON_ERROR_STOP=1' with sql 'WAIT
> > > > > > FOR LSN '0/03467F58' WITH (MODE 'standby_replay', timeout '180s',
> > > > > > no_throw);' at /Users/admin/pgsql/src/test/perl/PostgreSQL/Test/Cluster.pm
> > > > > > line 2300.
> > > > > >
> > > > > > https://cirrus-ci.com/task/5771274900733952
> > > > > >
> > > > > > The master branch in time-descending order, macOS tasks only:
> > > > > >
> > > > > >      task_id      | substring |  status
> > > > > > ------------------+-----------+-----------
> > > > > >  6460882231754752 | c970bdc0  | FAILED
> > > > > >  5771274900733952 | 6ca8506e  | FAILED
> > > > > >  6217757068361728 | 63ed3bc7  | FAILED
> > > > > >  5980650261446656 | ae283736  | FAILED
> > > > > >  6585898394976256 | 5f13999a  | COMPLETED
> > > > > >  4527474786172928 | 7f9acc9b  | COMPLETED
> > > > > >  4826100842364928 | e8d4e94a  | COMPLETED
> > > > > >  4540563027918848 | b9ee5f2d  | FAILED
> > > > > >  6358528648019968 | c5af141c  | FAILED
> > > > > >  5998005284765696 | e212a0f8  | COMPLETED
> > > > > >  6488580526178304 | b85d5dc0  | FAILED
> > > > > >  5034091344560128 | 7dc95cc3  | ABORTED
> > > > > >  5688692477526016 | bb048e31  | COMPLETED
> > > > > >  5481187977723904 | d351063e  | COMPLETED
> > > > > >  5101831568752640 | f30848cb  | COMPLETED <-- the change
> > > > > >  6395317408497664 | 3f33b63d  | COMPLETED
> > > > > >  6741325208354816 | 877ae5db  | COMPLETED
> > > > > >  4594007789010944 | de746e0d  | COMPLETED
> > > > > >  6497208998035456 | 461b8cc9  | COMPLETED
> > > > >
> > > > > Thanks for raising this issue. I think it is related to f30848cb after
> > > > > some analysis. I'll prepare a follow-up patch to fix it.
> > > >
> > > > Sorry, I've mistakenly referenced this report from commit [1].  I
> > > > thought it was related, but it appears to be not.  [1] is related to
> > > > the report I've got from Ruikai Peng off-list.
> > > >
> > > > Regarding the present failure, could it happen before ExecWaitStmt()
> > > > calls PopActiveSnapshot() and InvalidateCatalogSnapshot()?  If so, we
> > > > should do preliminary efforts to release these snapshots.
> > > >
> > > > 1. https://git.postgresql.org/pg/commitdiff/bf308639bfcfa38541e24733e074184153a8ab7f
> > > >
> > >
> > > I agree that moving PopActiveSnapshot() and
> > > InvalidateCatalogSnapshot() to the very beginning of ExecWaitStmt()
> > > appears to be a sensible optimization. However, in this particular
> > > failure scenario, it may not address the issue.
> > >
> > > For tablespace conflicts, recovery conflict resolution uses
> > > GetConflictingVirtualXIDs(InvalidTransactionId, InvalidOid), which
> > > returns all active backends, regardless of their snapshot state. As a
> > > result, even if all snapshots are released at the start of
> > > ExecWaitStmt(), the session would still be canceled during replay of
> > > DROP TABLESPACE.
> >
> > GetConflictingVirtualXIDs() uses proc->xmin to detect the conflicts.
> > ExecWaitStmt() asserts MyProc->xmin == InvalidTransactionId after
> > releasing all the snapshots.  I still think this happens because
> > conflict handling happens before ExecWaitStmt() manages to release the
> > snapshots.
> >
>
> I did not notice this message before. I'll look more closely at this case.

# VACUUM FREEZE, pruning those dead tuples
$node_primary->safe_psql($test_db, qq[VACUUM FREEZE $table1;]);

# Wait for attempted replay of PRUNE records
$node_primary->wait_for_replay_catchup($node_standby);

check_conflict_log(
"User query might have needed to see row versions that must be removed");
$psql_standby->reconnect_and_clear();
check_conflict_stat("snapshot");

Yeah, this code path could be problematic for the conflict type
PROCSIG_RECOVERY_CONFLICT_SNAPSHOT.  I created a patch to reduce the
false conflict detecting window as you suggested. Please check it too.


--
Best,
Xuneng

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Clean up 019_replslot_limit.pl comments

  2. Stabilize 019_replslot_limit.pl: wait on slot restart_lsn

  3. Fix memory ordering in WAIT FOR LSN wakeup mechanism

  4. Improve WAIT FOR LSN test coverage

  5. Remove redundant WAIT FOR LSN caller-side pre-checks

  6. Use barrier semantics when reading/writing writtenUpto

  7. Use replay position as floor for WAIT FOR LSN standby_(write|flush)

  8. Wake standby_write/standby_flush waiters from the WAL replay loop

  9. Minimal fix for WAIT FOR ... MODE 'standby_flush'

  10. Avoid syscache lookup while building a WAIT FOR tuple descriptor

  11. Document that WAIT FOR may be interrupted by recovery conflicts

  12. Use WAIT FOR LSN in PostgreSQL::Test::Cluster::wait_for_catchup()

  13. Wake LSN waiters before recovery target stop

  14. Remove redundant pg_unreachable() after elog(ERROR) from ExecWaitStmt()

  15. Revert "Use WAIT FOR LSN in PostgreSQL::Test::Cluster::wait_for_catchup()"

  16. Fix variable usage in wakeupWaiters()

  17. Add tab completion for the WAIT FOR LSN MODE option

  18. Add the MODE option to the WAIT FOR LSN command

  19. Extend xlogwait infrastructure with write and flush wait types

  20. Unify error messages

  21. Optimize shared memory usage for WaitLSNProcInfo

  22. Fix WaitLSNWakeup() fast-path check for InvalidXLogRecPtr

  23. Fix incorrect function name in comments

  24. Add infrastructure for efficient LSN waiting

  25. Add pairingheap_initialize() for shared memory usage

  26. Implement WAIT FOR command