On Mon, Mar 18, 2024 at 3:24 PM Alexander Korotkov <aekorot...@gmail.com> wrote: > > On Mon, Mar 18, 2024 at 5:17 AM Amit Kapila <amit.kapil...@gmail.com> wrote: > > > 1. First, check that it was called with non-atomic context (that is, > > > it's not called within a transaction). Trigger error if called with > > > atomic context. > > > 2. Release a snapshot to be able to wait without risk of WAL replay > > > stuck. Procedure is still called within the snapshot. It's a bit of > > > a hack to release a snapshot, but Vacuum statements already do so. > > > > > > > Can you please provide a bit more details with some example what is > > the existing problem with functions and how using procedures will > > resolve it? How will this this address the implicit transaction case > > or do we have any other workaround for those cases? > > Please check [1] and [2] for the explanation of the problem with functions. > > Also, please find a draft patch implementing the procedure. The issue with > the snapshot is addressed with the following lines. > > We first ensure we're in a non-atomic context, then pop an active snapshot > (tricky, but ExecuteVacuum() does the same). Then we should have no active > snapshot and it's safe to wait for lsn replay. > > if (context->atomic) > ereport(ERROR, > (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), > errmsg("pg_wait_lsn() must be only called in non-atomic > context"))); > > if (ActiveSnapshotSet()) > PopActiveSnapshot(); > Assert(!ActiveSnapshotSet()); > > The function call could be added either before the BEGIN statement or before > the implicit transaction. > > CALL pg_wait_lsn('my_lsn', my_timeout); BEGIN; > CALL pg_wait_lsn('my_lsn', my_timeout); SELECT ...; >
I haven't thought in detail about whether there are any other problems with this idea but sounds like it should solve the problems you shared with a function call approach. BTW, if the application has to anyway know the LSN till where replica needs to wait, why can't they simply monitor the pg_last_wal_replay_lsn() value? -- With Regards, Amit Kapila.