Horton

Thread

Commits

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

  1. Revert pg_wal_replay_wait() stored procedure

  2. Add 'no_error' argument to pg_wal_replay_wait()

  3. Refactor WaitForLSNReplay() to return the result of waiting

  4. Make WaitForLSNReplay() issue FATAL on postmaster death

  5. Move LSN waiting declarations and definitions to better place

  6. Update oid for pg_wal_replay_wait() procedure

  7. Move pg_wal_replay_wait() to xlogfuncs.c

  8. Implement pg_wal_replay_wait() stored procedure

  1. pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <akorotkov@postgresql.org> — 2024-08-02T18:22:21Z

    Implement pg_wal_replay_wait() stored procedure

pg_wal_replay_wait() is to be used on standby and specifies waiting for
the specific WAL location to be replayed.  This option is useful when
the user makes some data changes on primary and needs a guarantee to see
these changes are on standby.

The queue of waiters is stored in the shared memory as an LSN-ordered pairing
heap, where the waiter with the nearest LSN stays on the top.  During
the replay of WAL, waiters whose LSNs have already been replayed are deleted
from the shared memory pairing heap and woken up by setting their latches.

pg_wal_replay_wait() needs to wait without any snapshot held.  Otherwise,
the snapshot could prevent the replay of WAL records, implying a kind of
self-deadlock.  This is why it is only possible to implement
pg_wal_replay_wait() as a procedure working without an active snapshot,
not a function.

Catversion is bumped.

Discussion: https://postgr.es/m/eb12f9b03851bb2583adab5df9579b4b%40postgrespro.ru
Author: Kartyshov Ivan, Alexander Korotkov
Reviewed-by: Michael Paquier, Peter Eisentraut, Dilip Kumar, Amit Kapila
Reviewed-by: Alexander Lakhin, Bharath Rupireddy, Euler Taveira
Reviewed-by: Heikki Linnakangas, Kyotaro Horiguchi

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/3c5db1d6b01642bcd8dbf5e34b68f034365747bb

Modified Files
--------------
doc/src/sgml/func.sgml                          | 117 ++++++++
src/backend/access/transam/xact.c               |   6 +
src/backend/access/transam/xlog.c               |   7 +
src/backend/access/transam/xlogrecovery.c       |  11 +
src/backend/catalog/system_functions.sql        |   3 +
src/backend/commands/Makefile                   |   3 +-
src/backend/commands/meson.build                |   1 +
src/backend/commands/waitlsn.c                  | 363 ++++++++++++++++++++++++
src/backend/lib/pairingheap.c                   |  18 +-
src/backend/storage/ipc/ipci.c                  |   3 +
src/backend/storage/lmgr/proc.c                 |   6 +
src/backend/tcop/pquery.c                       |   9 +-
src/backend/utils/activity/wait_event_names.txt |   2 +
src/include/catalog/catversion.h                |   2 +-
src/include/catalog/pg_proc.dat                 |   6 +
src/include/commands/waitlsn.h                  |  80 ++++++
src/include/lib/pairingheap.h                   |   3 +
src/include/storage/lwlocklist.h                |   1 +
src/test/recovery/meson.build                   |   1 +
src/test/recovery/t/043_wal_replay_wait.pl      | 150 ++++++++++
src/tools/pgindent/typedefs.list                |   2 +
21 files changed, 786 insertions(+), 8 deletions(-)

  2. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Peter Eisentraut <peter@eisentraut.org> — 2024-08-30T19:42:20Z

    On 02.08.24 20:22, Alexander Korotkov wrote:
> Implement pg_wal_replay_wait() stored procedure

Why is this under src/backend/command/?  Wouldn't it belong under 
src/backend/utils/adt/?

> pg_wal_replay_wait() is to be used on standby and specifies waiting for
> the specific WAL location to be replayed.  This option is useful when
> the user makes some data changes on primary and needs a guarantee to see
> these changes are on standby.
> 
> The queue of waiters is stored in the shared memory as an LSN-ordered pairing
> heap, where the waiter with the nearest LSN stays on the top.  During
> the replay of WAL, waiters whose LSNs have already been replayed are deleted
> from the shared memory pairing heap and woken up by setting their latches.
> 
> pg_wal_replay_wait() needs to wait without any snapshot held.  Otherwise,
> the snapshot could prevent the replay of WAL records, implying a kind of
> self-deadlock.  This is why it is only possible to implement
> pg_wal_replay_wait() as a procedure working without an active snapshot,
> not a function.
> 
> Catversion is bumped.
> 
> Discussion: https://postgr.es/m/eb12f9b03851bb2583adab5df9579b4b%40postgrespro.ru
> Author: Kartyshov Ivan, Alexander Korotkov
> Reviewed-by: Michael Paquier, Peter Eisentraut, Dilip Kumar, Amit Kapila
> Reviewed-by: Alexander Lakhin, Bharath Rupireddy, Euler Taveira
> Reviewed-by: Heikki Linnakangas, Kyotaro Horiguchi
> 
> Branch
> ------
> master
> 
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/3c5db1d6b01642bcd8dbf5e34b68f034365747bb
> 
> Modified Files
> --------------
> doc/src/sgml/func.sgml                          | 117 ++++++++
> src/backend/access/transam/xact.c               |   6 +
> src/backend/access/transam/xlog.c               |   7 +
> src/backend/access/transam/xlogrecovery.c       |  11 +
> src/backend/catalog/system_functions.sql        |   3 +
> src/backend/commands/Makefile                   |   3 +-
> src/backend/commands/meson.build                |   1 +
> src/backend/commands/waitlsn.c                  | 363 ++++++++++++++++++++++++
> src/backend/lib/pairingheap.c                   |  18 +-
> src/backend/storage/ipc/ipci.c                  |   3 +
> src/backend/storage/lmgr/proc.c                 |   6 +
> src/backend/tcop/pquery.c                       |   9 +-
> src/backend/utils/activity/wait_event_names.txt |   2 +
> src/include/catalog/catversion.h                |   2 +-
> src/include/catalog/pg_proc.dat                 |   6 +
> src/include/commands/waitlsn.h                  |  80 ++++++
> src/include/lib/pairingheap.h                   |   3 +
> src/include/storage/lwlocklist.h                |   1 +
> src/test/recovery/meson.build                   |   1 +
> src/test/recovery/t/043_wal_replay_wait.pl      | 150 ++++++++++
> src/tools/pgindent/typedefs.list                |   2 +
> 21 files changed, 786 insertions(+), 8 deletions(-)
>

  3. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-01T19:35:27Z

    On Fri, Aug 30, 2024 at 10:42 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> On 02.08.24 20:22, Alexander Korotkov wrote:
> > Implement pg_wal_replay_wait() stored procedure
>
> Why is this under src/backend/command/?  Wouldn't it belong under
> src/backend/utils/adt/?

This path hasn't changes since the patch revision when it was a
utility command.  I agree that this doesn't look like proper path for
stored procedure.  But I don't think src/backend/utils/adt is
appropriate path either, because it's not really about data type.
pg_wal_replay_wait() looks a good neighbor for
pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
functions.  So, what about moving it to src/backend/access/transam?

------
Regards,
Alexander Korotkov
Supabase

  4. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-09-01T23:27:50Z

    On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
> This path hasn't changes since the patch revision when it was a
> utility command.  I agree that this doesn't look like proper path for
> stored procedure.  But I don't think src/backend/utils/adt is
> appropriate path either, because it's not really about data type.
> pg_wal_replay_wait() looks a good neighbor for
> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
> functions.  So, what about moving it to src/backend/access/transam?

Moving the new function to xlogfuncs.c while publishing
WaitForLSNReplay() makes sense to me.
--
Michael

  5. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-01T23:55:50Z

    On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
> > This path hasn't changes since the patch revision when it was a
> > utility command.  I agree that this doesn't look like proper path for
> > stored procedure.  But I don't think src/backend/utils/adt is
> > appropriate path either, because it's not really about data type.
> > pg_wal_replay_wait() looks a good neighbor for
> > pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
> > functions.  So, what about moving it to src/backend/access/transam?
>
> Moving the new function to xlogfuncs.c while publishing
> WaitForLSNReplay() makes sense to me.

Thank you for proposal.  I like this.

Could you, please, check the attached patch?

------
Regards,
Alexander Korotkov
Supabase

  6. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-09-02T00:24:55Z

    On Mon, Sep 02, 2024 at 02:55:50AM +0300, Alexander Korotkov wrote:
> Could you, please, check the attached patch?

The patch moving the code looks correct at quick glance.

Now, I've been staring at this line, wondering why this is required
while WaitForLSNReplay() does not return any status:
+   (void) WaitForLSNReplay(target_lsn, timeout);

And this makes me question whether you have the right semantics
regarding the SQL function itself.  Could it be more useful for
WaitForLSNReplay() to report an enum state that tells you the reason
why a wait may not have happened with a text or a tuple returned by
the function?  There are quite a few reasons why a wait may or may not
happen: 
- Recovery has ended, target LSN has been reached.
- We're not in recovery anymore.  Is an error the most useful thing
here actually?
- Timeout may have been reached, where an error is also generated.
ERRCODE_QUERY_CANCELED is not a correct error state.
- Perhaps more of these in the future?

My point is: this is a feature that can be used for monitoring as far
as I know, still it does not stick as a feature that could be most
useful for such applications.  Wouldn't it be more useful for client
applications to deal with a state returned by the SQL function rather
than having to parse error strings to know what happened?  What is
returned by pg_wal_replay_wal() reflects on the design of
WaitForLSNReplay() itself.
--
Michael

  7. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-03T13:07:11Z

    Hi, Michael!

On Mon, Sep 2, 2024 at 3:25 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Sep 02, 2024 at 02:55:50AM +0300, Alexander Korotkov wrote:
> > Could you, please, check the attached patch?
>
> The patch moving the code looks correct at quick glance.

Thank you for your feedback.

> Now, I've been staring at this line, wondering why this is required
> while WaitForLSNReplay() does not return any status:
> +   (void) WaitForLSNReplay(target_lsn, timeout);
>
> And this makes me question whether you have the right semantics
> regarding the SQL function itself.  Could it be more useful for
> WaitForLSNReplay() to report an enum state that tells you the reason
> why a wait may not have happened with a text or a tuple returned by
> the function?  There are quite a few reasons why a wait may or may not
> happen:
> - Recovery has ended, target LSN has been reached.
> - We're not in recovery anymore.  Is an error the most useful thing
> here actually?
> - Timeout may have been reached, where an error is also generated.
> ERRCODE_QUERY_CANCELED is not a correct error state.
> - Perhaps more of these in the future?
>
> My point is: this is a feature that can be used for monitoring as far
> as I know, still it does not stick as a feature that could be most
> useful for such applications.  Wouldn't it be more useful for client
> applications to deal with a state returned by the SQL function rather
> than having to parse error strings to know what happened?  What is
> returned by pg_wal_replay_wal() reflects on the design of
> WaitForLSNReplay() itself.

By design, pg_wal_replay_wal() should be followed up with read-only
query to standby.  The read-only query gets guarantee that it's
executed after the replay of LSN specified as pg_wal_replay_wal()
design.  Returning the status from pg_wal_replay_wal() would require
additional cycle of its processing.  But one of the main objectives of
this feature was reducing roundtrips during waiting for LSN.

On the other hand, I see that returning status could make sense for
certain use cases.  I think I could write two patches to provide that.
1. Make WaitForLSNReplay() return status, and make pg_wal_replay_wal()
be responsible for throwing all the errors.
2. New procedure pg_wal_replay_wal_status() (or some better name?),
which returns status to the user instead of throwing errors.

If no objections, I will push the patch moving code then go ahead
writing the two patches above.

------
Regards,
Alexander Korotkov
Supabase

  8. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-03T13:45:00Z

    On Tue, Sep 3, 2024 at 4:07 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> If no objections, I will push the patch moving code then go ahead
> writing the two patches above.

The patch for code movement missed couple of includes.  Revised
version is attached.

------
Regards,
Alexander Korotkov
Supabase

  9. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-19T12:47:24Z

    On Tue, Sep 3, 2024 at 4:07 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On the other hand, I see that returning status could make sense for
> certain use cases.  I think I could write two patches to provide that.
> 1. Make WaitForLSNReplay() return status, and make pg_wal_replay_wal()
> be responsible for throwing all the errors.
> 2. New procedure pg_wal_replay_wal_status() (or some better name?),
> which returns status to the user instead of throwing errors.
>
> If no objections, I will push the patch moving code then go ahead
> writing the two patches above.

I attempted to implement the patchset as promised.  The 0001 is easy
and straighforward.  The 0002 became tricky.  Procedures can't return
values.  They can have OUTPUT parameters instead.  However, even for
output parameters you need to pass something in, and that couldn't be
a default value.  Additional difficulty is that having OUTPUT
parameters seem to hold additional snapshot and prevents our
snapshot-releasing trick from working....

smagen@postgres=# CALL pg_wal_replay_wait('0/0'::pg_lsn);
CALL
Time: 2.061 ms
smagen@postgres=# CALL pg_wal_replay_wait_status(NULL, '0/0'::pg_lsn);
ERROR:  pg_wal_replay_wait_status() must be only called without an
active or registered snapshot
DETAIL:  Make sure pg_wal_replay_wait_status() isn't called within a
transaction with an isolation level higher than READ COMMITTED,
another procedure, or a function.
Time: 1.175 ms

I'm thinking about following solution:
1. pg_wal_replay_wait_no_error() procedure, which doesn't return
anything but throws no errors.
2. Make pg_wal_replay_wait()/pg_wal_replay_wait_no_error() save
waiting result status to the local variable.
3. New function pg_wal_replay_wal_get_status() displaying the result
status of the last pg_wal_replay_wait()/pg_wal_replay_wait_no_error()
CALL.

Then one could do.
CALL pg_wal_replay_wait_no_error(...);
SELECT pg_wal_replay_wal_get_status();

Probably looks a bit excessive, but probably the best we can do.

------
Regards,
Alexander Korotkov
Supabase

  10. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-20T12:00:20Z

    On Thu, Sep 19, 2024 at 3:47 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Tue, Sep 3, 2024 at 4:07 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > On the other hand, I see that returning status could make sense for
> > certain use cases.  I think I could write two patches to provide that.
> > 1. Make WaitForLSNReplay() return status, and make pg_wal_replay_wal()
> > be responsible for throwing all the errors.
> > 2. New procedure pg_wal_replay_wal_status() (or some better name?),
> > which returns status to the user instead of throwing errors.
> >
> > If no objections, I will push the patch moving code then go ahead
> > writing the two patches above.
>
> I attempted to implement the patchset as promised.  The 0001 is easy
> and straighforward.  The 0002 became tricky.  Procedures can't return
> values.  They can have OUTPUT parameters instead.  However, even for
> output parameters you need to pass something in, and that couldn't be
> a default value.  Additional difficulty is that having OUTPUT
> parameters seem to hold additional snapshot and prevents our
> snapshot-releasing trick from working....
>
> smagen@postgres=# CALL pg_wal_replay_wait('0/0'::pg_lsn);
> CALL
> Time: 2.061 ms
> smagen@postgres=# CALL pg_wal_replay_wait_status(NULL, '0/0'::pg_lsn);
> ERROR:  pg_wal_replay_wait_status() must be only called without an
> active or registered snapshot
> DETAIL:  Make sure pg_wal_replay_wait_status() isn't called within a
> transaction with an isolation level higher than READ COMMITTED,
> another procedure, or a function.
> Time: 1.175 ms
>
> I'm thinking about following solution:
> 1. pg_wal_replay_wait_no_error() procedure, which doesn't return
> anything but throws no errors.
> 2. Make pg_wal_replay_wait()/pg_wal_replay_wait_no_error() save
> waiting result status to the local variable.
> 3. New function pg_wal_replay_wal_get_status() displaying the result
> status of the last pg_wal_replay_wait()/pg_wal_replay_wait_no_error()
> CALL.
>
> Then one could do.
> CALL pg_wal_replay_wait_no_error(...);
> SELECT pg_wal_replay_wal_get_status();
>
> Probably looks a bit excessive, but probably the best we can do.

Please, check the attached patchset for implementation of proposed approach.

------
Regards,
Alexander Korotkov
Supabase

  11. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-09-26T08:18:49Z

    Hi Alexander,

On Fri, Aug 02, 2024 at 06:22:21PM +0000, Alexander Korotkov wrote:
> Implement pg_wal_replay_wait() stored procedure
> 
> pg_wal_replay_wait() is to be used on standby and specifies waiting for
> the specific WAL location to be replayed.  This option is useful when
> the user makes some data changes on primary and needs a guarantee to see
> these changes are on standby.
> 
> The queue of waiters is stored in the shared memory as an LSN-ordered pairing
> heap, where the waiter with the nearest LSN stays on the top.  During
> the replay of WAL, waiters whose LSNs have already been replayed are deleted
> from the shared memory pairing heap and woken up by setting their latches.
> 
> pg_wal_replay_wait() needs to wait without any snapshot held.  Otherwise,
> the snapshot could prevent the replay of WAL records, implying a kind of
> self-deadlock.  This is why it is only possible to implement
> pg_wal_replay_wait() as a procedure working without an active snapshot,
> not a function.
> 
> Catversion is bumped.

I've spotted that this patch uses an OID that should not be used
during the development cycle:
+{ oid => '111',
+  descr => 'wait for the target LSN to be replayed on standby with an optional timeout', 

Please use something in the 8000-9999 range, as required by
98eab30b93d5.

Thanks,
--
Michael

  12. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-26T15:41:18Z

    On Thu, Sep 26, 2024 at 11:19 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Fri, Aug 02, 2024 at 06:22:21PM +0000, Alexander Korotkov wrote:
> > Implement pg_wal_replay_wait() stored procedure
> >
> > pg_wal_replay_wait() is to be used on standby and specifies waiting for
> > the specific WAL location to be replayed.  This option is useful when
> > the user makes some data changes on primary and needs a guarantee to see
> > these changes are on standby.
> >
> > The queue of waiters is stored in the shared memory as an LSN-ordered pairing
> > heap, where the waiter with the nearest LSN stays on the top.  During
> > the replay of WAL, waiters whose LSNs have already been replayed are deleted
> > from the shared memory pairing heap and woken up by setting their latches.
> >
> > pg_wal_replay_wait() needs to wait without any snapshot held.  Otherwise,
> > the snapshot could prevent the replay of WAL records, implying a kind of
> > self-deadlock.  This is why it is only possible to implement
> > pg_wal_replay_wait() as a procedure working without an active snapshot,
> > not a function.
> >
> > Catversion is bumped.
>
> I've spotted that this patch uses an OID that should not be used
> during the development cycle:
> +{ oid => '111',
> +  descr => 'wait for the target LSN to be replayed on standby with an optional timeout',
>
> Please use something in the 8000-9999 range, as required by
> 98eab30b93d5.

Fixed, sorry for messing this up.
I would appreciate if you have time to look at [0] to check if it
meets your expectations.

Links.
1. https://www.postgresql.org/message-id/CAPpHfdsw9oq62Fvt65JApHJf1auUirdGJV7%3DnRyVnDL3M8z5xA%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase

  13. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-09-26T22:04:19Z

    On Thu, Sep 26, 2024 at 06:41:18PM +0300, Alexander Korotkov wrote:
> On Thu, Sep 26, 2024 at 11:19 AM Michael Paquier <michael@paquier.xyz> wrote:
>> Please use something in the 8000-9999 range, as required by
>> 98eab30b93d5.
> 
> Fixed, sorry for messing this up.

Thanks for taking care of that.

> I would appreciate if you have time to look at [0] to check if it
> meets your expectations.
> 
> Links.
> 1. https://www.postgresql.org/message-id/CAPpHfdsw9oq62Fvt65JApHJf1auUirdGJV7%3DnRyVnDL3M8z5xA%40mail.gmail.com

I am aware of this one, sorry for the delay.  It's on my pile of
things to look at, but I have not been able to get back to it.
--
Michael

  14. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-09-27T04:35:23Z

    On Fri, Sep 20, 2024 at 03:00:20PM +0300, Alexander Korotkov wrote:
> Please, check the attached patchset for implementation of proposed approach.

0001 looks like it requires an indentation in its .h diffs.

+typedef enum
+{
+	WaitLSNResultSuccess, /* Target LSN is reached */
+	WaitLSNResultTimeout, /* Timeout occured */

Perhaps use WAIT_LSN_RESULT_SUCCESS, etc, rather than camel casing.

+ * Results statuses for WaitForLSNReplay().

Too much plural here.

What you are doing with WaitForLSNReplay() sounds kind of right.

        rc = WaitLatch(MyLatch, wake_events, delay_ms,
             WAIT_EVENT_WAIT_FOR_WAL_REPLAY);

Question about this existing bit in waitlsn.c.  Shouldn't this issue a
FATAL if rc reports a WL_POSTMASTER_DEATH?  Or am I missing an
intention here.  That was already the case before this patch set.

pg_wal_replay_wait() is new to v18, meaning that we still have some
time to agree on its final shape for this release cycle.  This
discussion shares similarities with the recent exercise done in
f593c5517d14, and I think that we should be more consistent between
both to not repeat the same mistakes as in the past, even if this case
if more complex because we have more reasons behind why a wait could
not have happened.

I would suggest to keep things simple and have one single function
rather than introduce two more pg_proc entries with slight differences
in their error reporting, making the original function return a text
about the reason of the failure when waiting (be it a timeout,
success, promotion or outside recovery).

FWIW, I am confused regarding the need for WaitLSNResultNotInRecovery
and WaitLSNResultPromotedConcurrently in the error states.  On a code
basis, they check the same thing: RecoveryInProgress().  I'd suggest
to cut one of them.  This also points to the fact that
WaitForLSNReplay() has some duplication that's not required.  We could
have less GetXLogReplayRecPtr() calls and do all the checks in the for
loop.  The current structure of the code in waitlsn.c is more complex
than it could be.
--
Michael

  15. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-09-27T04:57:23Z

    On Fri, Sep 27, 2024 at 01:35:23PM +0900, Michael Paquier wrote:
> I would suggest to keep things simple and have one single function
> rather than introduce two more pg_proc entries with slight differences
> in their error reporting, making the original function return a text
> about the reason of the failure when waiting (be it a timeout,
> success, promotion or outside recovery).

More to the point here.  I am not sure what's the benefit of having a
procedure, while we have been using SQL functions for similar purposes
in xlogfuncs.c for years.  And what you have here can also be coupled
with more complex logic in SQL queries.
--
Michael

  16. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-27T14:07:09Z

    On Fri, Sep 27, 2024 at 7:57 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Fri, Sep 27, 2024 at 01:35:23PM +0900, Michael Paquier wrote:
> > I would suggest to keep things simple and have one single function
> > rather than introduce two more pg_proc entries with slight differences
> > in their error reporting, making the original function return a text
> > about the reason of the failure when waiting (be it a timeout,
> > success, promotion or outside recovery).
>
> More to the point here.  I am not sure what's the benefit of having a
> procedure, while we have been using SQL functions for similar purposes
> in xlogfuncs.c for years.  And what you have here can also be coupled
> with more complex logic in SQL queries.

As I multiple times pointed in the thread [1] [2], this couldn't be
done in SQL function.  SQL-function should run within snapshot, which
could prevent WAL from being replayed.  In turn, depending on timeout
settings that could lead to hidden deadlock (function waits for LSN to
be replayed, replay process wait snapshot to be released) or an error.

In the stored procedure, we're releasing active and catalog snapshots
(similarly to VACUUM statement).  Waiting without holding a snapshots
allows us to workaround the problem described above.  We can't do this
in the SQL function, because that would leave the rest of SQL query
without a snapshot.

Links.
1. https://www.postgresql.org/message-id/CAPpHfduBSN8j5j5Ynn5x%3DThD%3D8ypNd53D608VXGweBsPzxPvqA%40mail.gmail.com
2. https://www.postgresql.org/message-id/CAPpHfdtiGgn0iS1KbW2HTam-1%2BoK%2BvhXZDAcnX9hKaA7Oe%3DF-A%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase

  17. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-09-29T10:40:12Z

    Hi!

Thank you for your review.

On Fri, Sep 27, 2024 at 7:35 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Fri, Sep 20, 2024 at 03:00:20PM +0300, Alexander Korotkov wrote:
> > Please, check the attached patchset for implementation of proposed approach.
>
> 0001 looks like it requires an indentation in its .h diffs.
>
> +typedef enum
> +{
> +       WaitLSNResultSuccess, /* Target LSN is reached */
> +       WaitLSNResultTimeout, /* Timeout occured */
>
> Perhaps use WAIT_LSN_RESULT_SUCCESS, etc, rather than camel casing.
>
> + * Results statuses for WaitForLSNReplay().
>
> Too much plural here.
>
> What you are doing with WaitForLSNReplay() sounds kind of right.
>
>         rc = WaitLatch(MyLatch, wake_events, delay_ms,
>              WAIT_EVENT_WAIT_FOR_WAL_REPLAY);

Thank you, fixed in the attached patchset.

> Question about this existing bit in waitlsn.c.  Shouldn't this issue a
> FATAL if rc reports a WL_POSTMASTER_DEATH?  Or am I missing an
> intention here.  That was already the case before this patch set.

Fixed in the separate patch.

> pg_wal_replay_wait() is new to v18, meaning that we still have some
> time to agree on its final shape for this release cycle.  This
> discussion shares similarities with the recent exercise done in
> f593c5517d14, and I think that we should be more consistent between
> both to not repeat the same mistakes as in the past, even if this case
> if more complex because we have more reasons behind why a wait could
> not have happened.
>
> I would suggest to keep things simple and have one single function
> rather than introduce two more pg_proc entries with slight differences
> in their error reporting, making the original function return a text
> about the reason of the failure when waiting (be it a timeout,
> success, promotion or outside recovery).

I also like to keep things simple.  Keeping this as a single function
is not possible due to the reasons I described in [1].  However, it's
possible to fit into one stored procedure.  I made 'no_error' as an
argument for the pg_wal_replay_wait() procedure.  Done so in the
attached patchset.

> FWIW, I am confused regarding the need for WaitLSNResultNotInRecovery
> and WaitLSNResultPromotedConcurrently in the error states.  On a code
> basis, they check the same thing: RecoveryInProgress().  I'd suggest
> to cut one of them.

Agreed.  I initially intended to distinguish situations when the user
mistakenly calls pg_wal_replay_wait() on replication leader and when
concurrent promotion happens.  However, given that the promotion could
happen after the user issued the query and before waiting starts, it
doesn't make much sense.

>  This also points to the fact that
> WaitForLSNReplay() has some duplication that's not required.  We could
> have less GetXLogReplayRecPtr() calls and do all the checks in the for
> loop.  The current structure of the code in waitlsn.c is more complex
> than it could be.

Not sure about this.  I'd like to keep the fast-path check before we
call addLSNWaiter().

Links.
1. https://www.postgresql.org/message-id/CAPpHfdukVbJZntibZZ4HM7p92zN-QmAtD1%2BsAALRTFCsvpAq7A%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase

  18. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-11T23:33:11Z

    Hi!

On Sun, Sep 29, 2024 at 1:40 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> Thank you for your review.
>
> On Fri, Sep 27, 2024 at 7:35 AM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > On Fri, Sep 20, 2024 at 03:00:20PM +0300, Alexander Korotkov wrote:
> > > Please, check the attached patchset for implementation of proposed approach.
> >
> > 0001 looks like it requires an indentation in its .h diffs.
> >
> > +typedef enum
> > +{
> > +       WaitLSNResultSuccess, /* Target LSN is reached */
> > +       WaitLSNResultTimeout, /* Timeout occured */
> >
> > Perhaps use WAIT_LSN_RESULT_SUCCESS, etc, rather than camel casing.
> >
> > + * Results statuses for WaitForLSNReplay().
> >
> > Too much plural here.
> >
> > What you are doing with WaitForLSNReplay() sounds kind of right.
> >
> >         rc = WaitLatch(MyLatch, wake_events, delay_ms,
> >              WAIT_EVENT_WAIT_FOR_WAL_REPLAY);
>
> Thank you, fixed in the attached patchset.
>
> > Question about this existing bit in waitlsn.c.  Shouldn't this issue a
> > FATAL if rc reports a WL_POSTMASTER_DEATH?  Or am I missing an
> > intention here.  That was already the case before this patch set.
>
> Fixed in the separate patch.
>
> > pg_wal_replay_wait() is new to v18, meaning that we still have some
> > time to agree on its final shape for this release cycle.  This
> > discussion shares similarities with the recent exercise done in
> > f593c5517d14, and I think that we should be more consistent between
> > both to not repeat the same mistakes as in the past, even if this case
> > if more complex because we have more reasons behind why a wait could
> > not have happened.
> >
> > I would suggest to keep things simple and have one single function
> > rather than introduce two more pg_proc entries with slight differences
> > in their error reporting, making the original function return a text
> > about the reason of the failure when waiting (be it a timeout,
> > success, promotion or outside recovery).
>
> I also like to keep things simple.  Keeping this as a single function
> is not possible due to the reasons I described in [1].  However, it's
> possible to fit into one stored procedure.  I made 'no_error' as an
> argument for the pg_wal_replay_wait() procedure.  Done so in the
> attached patchset.
>
> > FWIW, I am confused regarding the need for WaitLSNResultNotInRecovery
> > and WaitLSNResultPromotedConcurrently in the error states.  On a code
> > basis, they check the same thing: RecoveryInProgress().  I'd suggest
> > to cut one of them.
>
> Agreed.  I initially intended to distinguish situations when the user
> mistakenly calls pg_wal_replay_wait() on replication leader and when
> concurrent promotion happens.  However, given that the promotion could
> happen after the user issued the query and before waiting starts, it
> doesn't make much sense.
>
> >  This also points to the fact that
> > WaitForLSNReplay() has some duplication that's not required.  We could
> > have less GetXLogReplayRecPtr() calls and do all the checks in the for
> > loop.  The current structure of the code in waitlsn.c is more complex
> > than it could be.
>
> Not sure about this.  I'd like to keep the fast-path check before we
> call addLSNWaiter().

Please, check the revised patchset.  It contains more tests for new
function pg_wal_replay_wait_status() and changes for documentation.

------
Regards,
Alexander Korotkov
Supabase

  19. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Peter Eisentraut <peter@eisentraut.org> — 2024-10-16T19:35:05Z

    On 02.09.24 01:55, Alexander Korotkov wrote:
> On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz> wrote:
>> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
>>> This path hasn't changes since the patch revision when it was a
>>> utility command.  I agree that this doesn't look like proper path for
>>> stored procedure.  But I don't think src/backend/utils/adt is
>>> appropriate path either, because it's not really about data type.
>>> pg_wal_replay_wait() looks a good neighbor for
>>> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
>>> functions.  So, what about moving it to src/backend/access/transam?
>>
>> Moving the new function to xlogfuncs.c while publishing
>> WaitForLSNReplay() makes sense to me.
> 
> Thank you for proposal.  I like this.
> 
> Could you, please, check the attached patch?

We still have stuff in src/backend/commands/waitlsn.c that is nothing 
like a "command".  You have moved some stuff elsewhere, but what are you 
planning to do with the rest?

  20. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-16T20:20:49Z

    On Wed, Oct 16, 2024 at 10:35 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> On 02.09.24 01:55, Alexander Korotkov wrote:
> > On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz> wrote:
> >> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
> >>> This path hasn't changes since the patch revision when it was a
> >>> utility command.  I agree that this doesn't look like proper path for
> >>> stored procedure.  But I don't think src/backend/utils/adt is
> >>> appropriate path either, because it's not really about data type.
> >>> pg_wal_replay_wait() looks a good neighbor for
> >>> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
> >>> functions.  So, what about moving it to src/backend/access/transam?
> >>
> >> Moving the new function to xlogfuncs.c while publishing
> >> WaitForLSNReplay() makes sense to me.
> >
> > Thank you for proposal.  I like this.
> >
> > Could you, please, check the attached patch?
>
> We still have stuff in src/backend/commands/waitlsn.c that is nothing
> like a "command".  You have moved some stuff elsewhere, but what are you
> planning to do with the rest?

Thank you for spotting this another time.  What about moving that
somewhere like src/backend/access/transam/xlogwait.c ?

------
Regards,
Alexander Korotkov
Supabase

  21. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-20T17:21:50Z

    On Wed, Oct 16, 2024 at 11:20 PM Alexander Korotkov
<aekorotkov@gmail.com> wrote:
>
> On Wed, Oct 16, 2024 at 10:35 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> > On 02.09.24 01:55, Alexander Korotkov wrote:
> > > On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz> wrote:
> > >> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
> > >>> This path hasn't changes since the patch revision when it was a
> > >>> utility command.  I agree that this doesn't look like proper path for
> > >>> stored procedure.  But I don't think src/backend/utils/adt is
> > >>> appropriate path either, because it's not really about data type.
> > >>> pg_wal_replay_wait() looks a good neighbor for
> > >>> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
> > >>> functions.  So, what about moving it to src/backend/access/transam?
> > >>
> > >> Moving the new function to xlogfuncs.c while publishing
> > >> WaitForLSNReplay() makes sense to me.
> > >
> > > Thank you for proposal.  I like this.
> > >
> > > Could you, please, check the attached patch?
> >
> > We still have stuff in src/backend/commands/waitlsn.c that is nothing
> > like a "command".  You have moved some stuff elsewhere, but what are you
> > planning to do with the rest?
>
> Thank you for spotting this another time.  What about moving that
> somewhere like src/backend/access/transam/xlogwait.c ?

Implemented this as a separate patch (0001).  Also rebased other
pending patches on that.  0004 now revises header comment of
xlogwait.c with new procedure signature.

------
Regards,
Alexander Korotkov
Supabase

  22. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Pavel Borisov <pashkin.elfe@gmail.com> — 2024-10-22T13:30:20Z

    Hi, Alexander!

On Tue, 22 Oct 2024 at 13:26, Alexander Korotkov <aekorotkov@gmail.com>
wrote:

> On Wed, Oct 16, 2024 at 11:20 PM Alexander Korotkov
> <aekorotkov@gmail.com> wrote:
> >
> > On Wed, Oct 16, 2024 at 10:35 PM Peter Eisentraut <peter@eisentraut.org>
> wrote:
> > > On 02.09.24 01:55, Alexander Korotkov wrote:
> > > > On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz>
> wrote:
> > > >> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
> > > >>> This path hasn't changes since the patch revision when it was a
> > > >>> utility command.  I agree that this doesn't look like proper path
> for
> > > >>> stored procedure.  But I don't think src/backend/utils/adt is
> > > >>> appropriate path either, because it's not really about data type.
> > > >>> pg_wal_replay_wait() looks a good neighbor for
> > > >>> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
> > > >>> functions.  So, what about moving it to src/backend/access/transam?
> > > >>
> > > >> Moving the new function to xlogfuncs.c while publishing
> > > >> WaitForLSNReplay() makes sense to me.
> > > >
> > > > Thank you for proposal.  I like this.
> > > >
> > > > Could you, please, check the attached patch?
> > >
> > > We still have stuff in src/backend/commands/waitlsn.c that is nothing
> > > like a "command".  You have moved some stuff elsewhere, but what are
> you
> > > planning to do with the rest?
> >
> > Thank you for spotting this another time.  What about moving that
> > somewhere like src/backend/access/transam/xlogwait.c ?
>
> Implemented this as a separate patch (0001).  Also rebased other
> pending patches on that.  0004 now revises header comment of
> xlogwait.c with new procedure signature.
>

I've looked at v5 of a patchset.

0001:
Looks completely ok.

0002:

As stated in latch.c

- WL_POSTMASTER_DEATH: Wait for postmaster to die
- WL_EXIT_ON_PM_DEATH: Exit immediately if the postmaster dies

 * wakeEvents must include either WL_EXIT_ON_PM_DEATH for automatic exit
 * if the postmaster dies or WL_POSTMASTER_DEATH for a flag set in the
 * return value if the postmaster dies

It's not completely clear to me if these comments need some clarification
(not related to the patchset), or if we should look for WL_EXIT_ON_PM_DEATH
for immediately FATAL. Or waiting for postmaster to die on
WL_POSTMASTER_DEATH instead of just fatal immediately?

0003:
Besides refactor it looks that deleteLSNWaiter() is added
in WaitForLSNReplay. Maybe it's worth mentioning in the commit message.
Maybe refactoring for loop for assigning result variable and breaking a
loop instead of immediate return would look better and lead to natural call
of after the loop before returning.

0004:

Comment:
+ * Waits until recovery replays the target LSN with optional timeout.
Throw
+ * an error on failure.
may need mentioning "Unless no_error provided throws an error on failure"

Otherwise the patch looks good.

Regards,
Pavel Borisov
Supabase

  23. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Pavel Borisov <pashkin.elfe@gmail.com> — 2024-10-22T13:32:51Z

    Fix a typo of myself:

> Maybe refactoring for loop for assigning result variable and breaking a
> loop instead of immediate return would look better and lead to natural call
> of after the loop before returning.
>
 Maybe refactoring for loop for assigning result variable and breaking a
loop instead of immediate return would look better and lead to natural call
of *deleteLSNWaiter()* after the loop before returning.

Pavel.

  24. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-22T20:12:27Z

    Hi, Pavel!

Thank you for your review.

On Tue, Oct 22, 2024 at 4:30 PM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
> On Tue, 22 Oct 2024 at 13:26, Alexander Korotkov <aekorotkov@gmail.com> wrote:
>>
>> On Wed, Oct 16, 2024 at 11:20 PM Alexander Korotkov
>> <aekorotkov@gmail.com> wrote:
>> >
>> > On Wed, Oct 16, 2024 at 10:35 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>> > > On 02.09.24 01:55, Alexander Korotkov wrote:
>> > > > On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz> wrote:
>> > > >> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
>> > > >>> This path hasn't changes since the patch revision when it was a
>> > > >>> utility command.  I agree that this doesn't look like proper path for
>> > > >>> stored procedure.  But I don't think src/backend/utils/adt is
>> > > >>> appropriate path either, because it's not really about data type.
>> > > >>> pg_wal_replay_wait() looks a good neighbor for
>> > > >>> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
>> > > >>> functions.  So, what about moving it to src/backend/access/transam?
>> > > >>
>> > > >> Moving the new function to xlogfuncs.c while publishing
>> > > >> WaitForLSNReplay() makes sense to me.
>> > > >
>> > > > Thank you for proposal.  I like this.
>> > > >
>> > > > Could you, please, check the attached patch?
>> > >
>> > > We still have stuff in src/backend/commands/waitlsn.c that is nothing
>> > > like a "command".  You have moved some stuff elsewhere, but what are you
>> > > planning to do with the rest?
>> >
>> > Thank you for spotting this another time.  What about moving that
>> > somewhere like src/backend/access/transam/xlogwait.c ?
>>
>> Implemented this as a separate patch (0001).  Also rebased other
>> pending patches on that.  0004 now revises header comment of
>> xlogwait.c with new procedure signature.
>
>
> I've looked at v5 of a patchset.
>
> 0001:
> Looks completely ok.
>
> 0002:
>
> As stated in latch.c
>
> - WL_POSTMASTER_DEATH: Wait for postmaster to die
> - WL_EXIT_ON_PM_DEATH: Exit immediately if the postmaster dies
>
>  * wakeEvents must include either WL_EXIT_ON_PM_DEATH for automatic exit
>  * if the postmaster dies or WL_POSTMASTER_DEATH for a flag set in the
>  * return value if the postmaster dies
>
> It's not completely clear to me if these comments need some clarification (not related to the patchset), or if we should look for WL_EXIT_ON_PM_DEATH for immediately FATAL. Or waiting for postmaster to die on WL_POSTMASTER_DEATH instead of just fatal immediately?

As I get from the code, WL_EXIT_ON_PM_DEATH cause process to just
proc_exit(1) without throwing FATAL.  So, in the most of situations we
do throw FATAL after seeing WL_POSTMASTER_DEATH event.  So, it's
reasonable to do the same here.  But indeed, this is a question (not
related to this patch) whether WL_EXIT_ON_PM_DEATH should cause
process to throw FATAL.

> 0003:
> Besides refactor it looks that deleteLSNWaiter() is added in WaitForLSNReplay. Maybe it's worth mentioning in the commit message.
> Maybe refactoring for loop for assigning result variable and breaking a loop instead of immediate return would look better and lead to natural call of after the loop before returning.

I don't think we would get much simplicity/readability by breaking
loop instead of immediate return.  However, I reflected the changes in
the commit message.  Also I reflected that we don't distinguish any
more seeing !RecoveryInProgress() in different places.

> 0004:
>
> Comment:
> + * Waits until recovery replays the target LSN with optional timeout.  Throw
> + * an error on failure.
> may need mentioning "Unless no_error provided throws an error on failure"

Changed as you proposed.

> Otherwise the patch looks good.

Thanks!

------
Regards,
Alexander Korotkov
Supabase

  25. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Pavel Borisov <pashkin.elfe@gmail.com> — 2024-10-23T06:01:49Z

    Hi, Alexander!

On Wed, 23 Oct 2024 at 00:12, Alexander Korotkov <aekorotkov@gmail.com>
wrote:

> Hi, Pavel!
>
> Thank you for your review.
>
> On Tue, Oct 22, 2024 at 4:30 PM Pavel Borisov <pashkin.elfe@gmail.com>
> wrote:
> > On Tue, 22 Oct 2024 at 13:26, Alexander Korotkov <aekorotkov@gmail.com>
> wrote:
> >>
> >> On Wed, Oct 16, 2024 at 11:20 PM Alexander Korotkov
> >> <aekorotkov@gmail.com> wrote:
> >> >
> >> > On Wed, Oct 16, 2024 at 10:35 PM Peter Eisentraut <
> peter@eisentraut.org> wrote:
> >> > > On 02.09.24 01:55, Alexander Korotkov wrote:
> >> > > > On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <
> michael@paquier.xyz> wrote:
> >> > > >> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov
> wrote:
> >> > > >>> This path hasn't changes since the patch revision when it was a
> >> > > >>> utility command.  I agree that this doesn't look like proper
> path for
> >> > > >>> stored procedure.  But I don't think src/backend/utils/adt is
> >> > > >>> appropriate path either, because it's not really about data
> type.
> >> > > >>> pg_wal_replay_wait() looks a good neighbor for
> >> > > >>> pg_wal_replay_pause()/pg_wal_replay_resume() and other
> WAL-related
> >> > > >>> functions.  So, what about moving it to
> src/backend/access/transam?
> >> > > >>
> >> > > >> Moving the new function to xlogfuncs.c while publishing
> >> > > >> WaitForLSNReplay() makes sense to me.
> >> > > >
> >> > > > Thank you for proposal.  I like this.
> >> > > >
> >> > > > Could you, please, check the attached patch?
> >> > >
> >> > > We still have stuff in src/backend/commands/waitlsn.c that is
> nothing
> >> > > like a "command".  You have moved some stuff elsewhere, but what
> are you
> >> > > planning to do with the rest?
> >> >
> >> > Thank you for spotting this another time.  What about moving that
> >> > somewhere like src/backend/access/transam/xlogwait.c ?
> >>
> >> Implemented this as a separate patch (0001).  Also rebased other
> >> pending patches on that.  0004 now revises header comment of
> >> xlogwait.c with new procedure signature.
> >
> >
> > I've looked at v5 of a patchset.
>
> > 0002:
> >
> > As stated in latch.c
> >
> > - WL_POSTMASTER_DEATH: Wait for postmaster to die
> > - WL_EXIT_ON_PM_DEATH: Exit immediately if the postmaster dies
> >
> >  * wakeEvents must include either WL_EXIT_ON_PM_DEATH for automatic exit
> >  * if the postmaster dies or WL_POSTMASTER_DEATH for a flag set in the
> >  * return value if the postmaster dies
> >
> > It's not completely clear to me if these comments need some
> clarification (not related to the patchset), or if we should look for
> WL_EXIT_ON_PM_DEATH for immediately FATAL. Or waiting for postmaster to die
> on WL_POSTMASTER_DEATH instead of just fatal immediately?
>
> As I get from the code, WL_EXIT_ON_PM_DEATH cause process to just
> proc_exit(1) without throwing FATAL.  So, in the most of situations we
> do throw FATAL after seeing WL_POSTMASTER_DEATH event.  So, it's
> reasonable to do the same here.  But indeed, this is a question (not
> related to this patch) whether WL_EXIT_ON_PM_DEATH should cause
> process to throw FATAL.
>

Libpq ends up with FATAL on WL_POSTMASTER_DEATH.
In a backend code on WL_POSTMASTER_DEATH: SyncRepWaitForLSN()
sets ProcDiePending but don't FATAL. Walsender exits proc_exit(1).
I suppose WL_POSTMASTER_DEATH expected behavior is "Do whatever you want:
wait for postmaster to die or end up immediately".
I think path 0002 is good.

I looked through patches v6 and I think they're all good now.

Regards,
Pavel Borisov
Supabase.

  26. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-23T16:21:02Z

    On Wed, Oct 23, 2024 at 9:02 AM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
> On Wed, 23 Oct 2024 at 00:12, Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> Thank you for your review.
>>
>> On Tue, Oct 22, 2024 at 4:30 PM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>> > On Tue, 22 Oct 2024 at 13:26, Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> >>
>> >> On Wed, Oct 16, 2024 at 11:20 PM Alexander Korotkov
>> >> <aekorotkov@gmail.com> wrote:
>> >> >
>> >> > On Wed, Oct 16, 2024 at 10:35 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>> >> > > On 02.09.24 01:55, Alexander Korotkov wrote:
>> >> > > > On Mon, Sep 2, 2024 at 2:28 AM Michael Paquier <michael@paquier.xyz> wrote:
>> >> > > >> On Sun, Sep 01, 2024 at 10:35:27PM +0300, Alexander Korotkov wrote:
>> >> > > >>> This path hasn't changes since the patch revision when it was a
>> >> > > >>> utility command.  I agree that this doesn't look like proper path for
>> >> > > >>> stored procedure.  But I don't think src/backend/utils/adt is
>> >> > > >>> appropriate path either, because it's not really about data type.
>> >> > > >>> pg_wal_replay_wait() looks a good neighbor for
>> >> > > >>> pg_wal_replay_pause()/pg_wal_replay_resume() and other WAL-related
>> >> > > >>> functions.  So, what about moving it to src/backend/access/transam?
>> >> > > >>
>> >> > > >> Moving the new function to xlogfuncs.c while publishing
>> >> > > >> WaitForLSNReplay() makes sense to me.
>> >> > > >
>> >> > > > Thank you for proposal.  I like this.
>> >> > > >
>> >> > > > Could you, please, check the attached patch?
>> >> > >
>> >> > > We still have stuff in src/backend/commands/waitlsn.c that is nothing
>> >> > > like a "command".  You have moved some stuff elsewhere, but what are you
>> >> > > planning to do with the rest?
>> >> >
>> >> > Thank you for spotting this another time.  What about moving that
>> >> > somewhere like src/backend/access/transam/xlogwait.c ?
>> >>
>> >> Implemented this as a separate patch (0001).  Also rebased other
>> >> pending patches on that.  0004 now revises header comment of
>> >> xlogwait.c with new procedure signature.
>> >
>> >
>> > I've looked at v5 of a patchset.
>>
>> > 0002:
>> >
>> > As stated in latch.c
>> >
>> > - WL_POSTMASTER_DEATH: Wait for postmaster to die
>> > - WL_EXIT_ON_PM_DEATH: Exit immediately if the postmaster dies
>> >
>> >  * wakeEvents must include either WL_EXIT_ON_PM_DEATH for automatic exit
>> >  * if the postmaster dies or WL_POSTMASTER_DEATH for a flag set in the
>> >  * return value if the postmaster dies
>> >
>> > It's not completely clear to me if these comments need some clarification (not related to the patchset), or if we should look for WL_EXIT_ON_PM_DEATH for immediately FATAL. Or waiting for postmaster to die on WL_POSTMASTER_DEATH instead of just fatal immediately?
>>
>> As I get from the code, WL_EXIT_ON_PM_DEATH cause process to just
>> proc_exit(1) without throwing FATAL.  So, in the most of situations we
>> do throw FATAL after seeing WL_POSTMASTER_DEATH event.  So, it's
>> reasonable to do the same here.  But indeed, this is a question (not
>> related to this patch) whether WL_EXIT_ON_PM_DEATH should cause
>> process to throw FATAL.
>
>
> Libpq ends up with FATAL on WL_POSTMASTER_DEATH.
> In a backend code on WL_POSTMASTER_DEATH: SyncRepWaitForLSN() sets ProcDiePending but don't FATAL. Walsender exits proc_exit(1).
> I suppose WL_POSTMASTER_DEATH expected behavior is "Do whatever you want: wait for postmaster to die or end up immediately".
> I think path 0002 is good.
>
> I looked through patches v6 and I think they're all good now.

Thank you.  I will push this patchset if now objections.

------
Regards,
Alexander Korotkov
Supabase

  27. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Heikki Linnakangas <hlinnaka@iki.fi> — 2024-10-25T06:06:10Z

    If you call this procedure on a stand-alone server, you get:

postgres=# call pg_wal_replay_wait('1234/0');
ERROR:  recovery is not in progress
DETAIL:  Recovery ended before replaying target LSN 1234/0; last replay 
LSN 0/0.

The DETAIL seems a bit misleading. Recovery never ended, because it 
never started in the first place. Last replay LSN is indeed 0/0, but 
that's not helpful.

If a standby server has been promoted and you pass an LSN that's earlier 
than the last replay LSN, it returns successfully. That makes sense I 
guess; if you connect to a standby and wait for it to replay a commit 
that you made in the primary, and the standby gets promoted, that seems 
correct. But it's a little inconsistent: If the standby crashes 
immediately after promotion, and you call pg_wal_replay_wait() after 
recovery, it returns success. However, if you shut down the promoted 
server and restart it, then last replay LSN is 0/0, and the call will 
fail because no recovery happened.

What is the use case for the 'no_error' argument? Why would you ever 
want to pass no_error=true ? The separate pg_wal_replay_wait_status() 
function feels weird to me. Also it surely shouldn't be marked IMMUTABLE 
nor parallel safe.

This would benefit from more documentation, explaining how you would use 
this in practice. I believe the use case is that you want "read your 
writes" consistency between a primary and a standby. You commit a 
transaction in the primary, and you then want to run read-only queries 
in a standby, and you want to make sure that you see your own commit, 
but you're ok with slightly delayed results otherwise. It would be good 
to add a chapter somewhere in the docs to show how to do that in 
practice with these functions.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  28. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-25T11:56:36Z

    Hi, Heikki!

On Fri, Oct 25, 2024 at 9:06 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> If you call this procedure on a stand-alone server, you get:
>
> postgres=# call pg_wal_replay_wait('1234/0');
> ERROR:  recovery is not in progress
> DETAIL:  Recovery ended before replaying target LSN 1234/0; last replay
> LSN 0/0.
>
> The DETAIL seems a bit misleading. Recovery never ended, because it
> never started in the first place. Last replay LSN is indeed 0/0, but
> that's not helpful.
>
> If a standby server has been promoted and you pass an LSN that's earlier
> than the last replay LSN, it returns successfully. That makes sense I
> guess; if you connect to a standby and wait for it to replay a commit
> that you made in the primary, and the standby gets promoted, that seems
> correct. But it's a little inconsistent: If the standby crashes
> immediately after promotion, and you call pg_wal_replay_wait() after
> recovery, it returns success. However, if you shut down the promoted
> server and restart it, then last replay LSN is 0/0, and the call will
> fail because no recovery happened.
>
> What is the use case for the 'no_error' argument? Why would you ever
> want to pass no_error=true ? The separate pg_wal_replay_wait_status()
> function feels weird to me. Also it surely shouldn't be marked IMMUTABLE
> nor parallel safe.
>
> This would benefit from more documentation, explaining how you would use
> this in practice. I believe the use case is that you want "read your
> writes" consistency between a primary and a standby. You commit a
> transaction in the primary, and you then want to run read-only queries
> in a standby, and you want to make sure that you see your own commit,
> but you're ok with slightly delayed results otherwise. It would be good
> to add a chapter somewhere in the docs to show how to do that in
> practice with these functions.

Thank you for your feedback!

I do agree that error reporting for "not in recovery" case needs to be
improved, as well, as the documentation.

I see that pg_wal_replay_wait_status() might look weird, but it seems
to me like the best of feasible solutions.  Given that
pg_wal_replay_wait() procedure can't work concurrently to a query
involving pg_wal_replay_wait_status() function, I think
pg_wal_replay_wait_status() should be stable and parallel safe.

This is the brief answer.  I will be able to come back with more
details on Monday.

------
Regards,
Alexander Korotkov
Supabase

  29. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Heikki Linnakangas <hlinnaka@iki.fi> — 2024-10-28T09:36:43Z

    On 25/10/2024 14:56, Alexander Korotkov wrote:
> I see that pg_wal_replay_wait_status() might look weird, but it seems
> to me like the best of feasible solutions. 

I haven't written many procedures, but our docs say:

 > Procedures do not return a function value; hence CREATE PROCEDURE 
lacks a RETURNS clause. However, procedures can instead return data to 
their callers via output parameters.

Did you consider using an output parameter?

> Given that
> pg_wal_replay_wait() procedure can't work concurrently to a query
> involving pg_wal_replay_wait_status() function, I think
> pg_wal_replay_wait_status() should be stable and parallel safe.

If you call pg_wal_replay_wait() in the backend process, and 
pg_wal_replay_wait_status() in a parallel worker process, it won't 
return the result of the wait. Probably not what you'd expect. So I'd 
argue that it should be parallel unsafe.

> This is the brief answer.  I will be able to come back with more
> details on Monday.

Thanks. A few more minor issues I spotted while playing with this:

- If you pass a very high value as the timeout, e.g. INT_MAX-1, it wraps 
around and doesn't wait at all
- You can pass NULLs as arguments. That should probably not be allowed, 
or we need to document what it means.

This is disappointing:

> postgres=# set default_transaction_isolation ='repeatable read';
> SET
> postgres=# call pg_wal_replay_wait('0/55DA24F');
> ERROR:  pg_wal_replay_wait() must be only called without an active or registered snapshot
> DETAIL:  Make sure pg_wal_replay_wait() isn't called within a transaction with an isolation level higher than READ COMMITTED, another procedure, or a function.

Is there any way we could make that work? Otherwise, the feature just 
basically doesn't work if you use repeatable read.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  30. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Robert Haas <robertmhaas@gmail.com> — 2024-10-28T15:47:19Z

    Point of order:

Discussions of commits and potential followup commits are best
redirected to -hackers rather than continuing to use -committers.

...Robert

  31. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-28T18:35:02Z

    On Mon, Oct 28, 2024 at 5:47 PM Robert Haas <robertmhaas@gmail.com> wrote:
> Point of order:
>
> Discussions of commits and potential followup commits are best
> redirected to -hackers rather than continuing to use -committers.

Thank you for pointing this.
My response to Heikki's last message will be redirected to -hackers.

------
Regards,
Alexander Korotkov
Supabase

  32. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-10-28T19:42:52Z

    On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> On 25/10/2024 14:56, Alexander Korotkov wrote:
> > I see that pg_wal_replay_wait_status() might look weird, but it seems
> > to me like the best of feasible solutions.
>
> I haven't written many procedures, but our docs say:
>
>  > Procedures do not return a function value; hence CREATE PROCEDURE
> lacks a RETURNS clause. However, procedures can instead return data to
> their callers via output parameters.
>
> Did you consider using an output parameter?

Yes I did consider them and found two issues.
1) You still need to pass something to them.  And that couldn't be
default values.  That's a bit awkward.
2) Usage of them causes extra snapshot to be held.
I'll recheck if it's possible to workaround any of these two.

> > Given that
> > pg_wal_replay_wait() procedure can't work concurrently to a query
> > involving pg_wal_replay_wait_status() function, I think
> > pg_wal_replay_wait_status() should be stable and parallel safe.
>
> If you call pg_wal_replay_wait() in the backend process, and
> pg_wal_replay_wait_status() in a parallel worker process, it won't
> return the result of the wait. Probably not what you'd expect. So I'd
> argue that it should be parallel unsafe.

Oh, sorry.  You're absolutely correct.  That should be parallel unsafe.

> > This is the brief answer.  I will be able to come back with more
> > details on Monday.
>
> Thanks. A few more minor issues I spotted while playing with this:
>
> - If you pass a very high value as the timeout, e.g. INT_MAX-1, it wraps
> around and doesn't wait at all
> - You can pass NULLs as arguments. That should probably not be allowed,
> or we need to document what it means.
>
> This is disappointing:
>
> > postgres=# set default_transaction_isolation ='repeatable read';
> > SET
> > postgres=# call pg_wal_replay_wait('0/55DA24F');
> > ERROR:  pg_wal_replay_wait() must be only called without an active or registered snapshot
> > DETAIL:  Make sure pg_wal_replay_wait() isn't called within a transaction with an isolation level higher than READ COMMITTED, another procedure, or a function.
>
> Is there any way we could make that work? Otherwise, the feature just
> basically doesn't work if you use repeatable read.

Thank you for catching this.  The last one is really disappointing.
I'm exploring on what could be done there.

------
Regards,
Alexander Korotkov
Supabase

  33. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-03T20:54:33Z

    Hi, Heikki!

On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
>
> On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinnaka@iki.fi>
wrote:
> >
> > On 25/10/2024 14:56, Alexander Korotkov wrote:
> > > I see that pg_wal_replay_wait_status() might look weird, but it seems
> > > to me like the best of feasible solutions.
> >
> > I haven't written many procedures, but our docs say:
> >
> >  > Procedures do not return a function value; hence CREATE PROCEDURE
> > lacks a RETURNS clause. However, procedures can instead return data to
> > their callers via output parameters.
> >
> > Did you consider using an output parameter?
>
> Yes I did consider them and found two issues.
> 1) You still need to pass something to them.  And that couldn't be
> default values.  That's a bit awkward.
> 2) Usage of them causes extra snapshot to be held.
> I'll recheck if it's possible to workaround any of these two.

I've rechecked the output parameters for stored procedures.  And I think
the behavior I previously discovered is an anomaly.

CREATE PROCEDURE test_proc(a integer, out b integer)
LANGUAGE plpgsql
AS $$
BEGIN
  b := a;
END;
$$;

# call test_proc(1);
ERROR:  procedure test_proc(integer) does not exist
LINE 1: call test_proc(1);
             ^
HINT:  No procedure matches the given name and argument types. You might
need to add explicit type casts.

# call test_proc(1,2);
 b
---
 1
(1 row)

Looks weird that we have to pass in some (ignored?) values for output
parameters.  In contrast, functions don't require this.

CREATE FUNCTION test_func(a integer, out b integer)
LANGUAGE plpgsql
AS $$
BEGIN
  b := a;
END;
$$;

# select  test_func(1);
 test_func
-----------
         1
(1 row)

This makes me think we have an issue with stored procedures here.  I'll try
to investigate it further.

Also when we have output parameters, PortalRun() have portal->strategy ==
PORTAL_UTIL_SELECT (it's PORTAL_MULTI_QUERY without them).  This eventually
makes PortalRunUtility() to do RegisterSnapshot().  This is not clear
whether we can release this snapshot without a stored procedure without the
consequences.

> > > Given that
> > > pg_wal_replay_wait() procedure can't work concurrently to a query
> > > involving pg_wal_replay_wait_status() function, I think
> > > pg_wal_replay_wait_status() should be stable and parallel safe.
> >
> > If you call pg_wal_replay_wait() in the backend process, and
> > pg_wal_replay_wait_status() in a parallel worker process, it won't
> > return the result of the wait. Probably not what you'd expect. So I'd
> > argue that it should be parallel unsafe.
>
> Oh, sorry.  You're absolutely correct.  That should be parallel unsafe.
>
> > > This is the brief answer.  I will be able to come back with more
> > > details on Monday.
> >
> > Thanks. A few more minor issues I spotted while playing with this:
> >
> > - If you pass a very high value as the timeout, e.g. INT_MAX-1, it wraps
> > around and doesn't wait at all
> > - You can pass NULLs as arguments. That should probably not be allowed,
> > or we need to document what it means.
> >
> > This is disappointing:
> >
> > > postgres=# set default_transaction_isolation ='repeatable read';
> > > SET
> > > postgres=# call pg_wal_replay_wait('0/55DA24F');
> > > ERROR:  pg_wal_replay_wait() must be only called without an active or
registered snapshot
> > > DETAIL:  Make sure pg_wal_replay_wait() isn't called within a
transaction with an isolation level higher than READ COMMITTED, another
procedure, or a function.
> >
> > Is there any way we could make that work? Otherwise, the feature just
> > basically doesn't work if you use repeatable read.
>
> Thank you for catching this.  The last one is really disappointing.
> I'm exploring on what could be done there.

If we set repeatable read as the default transaction isolation level, then
the catalog snapshot used to find pg_wal_replay_wait() procedure got saved
as the transaction snapshot.  As I get the correct way to release that
snapshot is to finish current transaction and start another one.
Implemented in the attached patch.

------
Regards,
Alexander Korotkov
Supabase

  34. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-03T21:03:38Z

    On Sun, Nov 3, 2024 at 10:54 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> > >
> > > On 25/10/2024 14:56, Alexander Korotkov wrote:
> > > > I see that pg_wal_replay_wait_status() might look weird, but it seems
> > > > to me like the best of feasible solutions.
> > >
> > > I haven't written many procedures, but our docs say:
> > >
> > >  > Procedures do not return a function value; hence CREATE PROCEDURE
> > > lacks a RETURNS clause. However, procedures can instead return data to
> > > their callers via output parameters.
> > >
> > > Did you consider using an output parameter?
> >
> > Yes I did consider them and found two issues.
> > 1) You still need to pass something to them.  And that couldn't be
> > default values.  That's a bit awkward.
> > 2) Usage of them causes extra snapshot to be held.
> > I'll recheck if it's possible to workaround any of these two.
>
> I've rechecked the output parameters for stored procedures.  And I think the behavior I previously discovered is an anomaly.
>
> CREATE PROCEDURE test_proc(a integer, out b integer)
> LANGUAGE plpgsql
> AS $$
> BEGIN
>   b := a;
> END;
> $$;
>
> # call test_proc(1);
> ERROR:  procedure test_proc(integer) does not exist
> LINE 1: call test_proc(1);
>              ^
> HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
>
> # call test_proc(1,2);
>  b
> ---
>  1
> (1 row)
>
> Looks weird that we have to pass in some (ignored?) values for output parameters.  In contrast, functions don't require this.
>
> CREATE FUNCTION test_func(a integer, out b integer)
> LANGUAGE plpgsql
> AS $$
> BEGIN
>   b := a;
> END;
> $$;
>
> # select  test_func(1);
>  test_func
> -----------
>          1
> (1 row)
>
> This makes me think we have an issue with stored procedures here.  I'll try to investigate it further.

Oh, this seems to be intentional [1] and seems to be part of standard [2].

Links
1. https://www.postgresql.org/docs/devel/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS-PROC
2. https://www.postgresql.org/message-id/2b8490fe-51af-e671-c504-47359dc453c5%402ndquadrant.com

------
Regards,
Alexander Korotkov
Supabase

  35. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-04T04:29:42Z

    On Sun, Nov 3, 2024 at 11:03 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Sun, Nov 3, 2024 at 10:54 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > >
> > > On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> > > >
> > > > On 25/10/2024 14:56, Alexander Korotkov wrote:
> > > > > I see that pg_wal_replay_wait_status() might look weird, but it seems
> > > > > to me like the best of feasible solutions.
> > > >
> > > > I haven't written many procedures, but our docs say:
> > > >
> > > >  > Procedures do not return a function value; hence CREATE PROCEDURE
> > > > lacks a RETURNS clause. However, procedures can instead return data to
> > > > their callers via output parameters.
> > > >
> > > > Did you consider using an output parameter?
> > >
> > > Yes I did consider them and found two issues.
> > > 1) You still need to pass something to them.  And that couldn't be
> > > default values.  That's a bit awkward.
> > > 2) Usage of them causes extra snapshot to be held.
> > > I'll recheck if it's possible to workaround any of these two.
> >
> > I've rechecked the output parameters for stored procedures.  And I think the behavior I previously discovered is an anomaly.
> >
> > CREATE PROCEDURE test_proc(a integer, out b integer)
> > LANGUAGE plpgsql
> > AS $$
> > BEGIN
> >   b := a;
> > END;
> > $$;
> >
> > # call test_proc(1);
> > ERROR:  procedure test_proc(integer) does not exist
> > LINE 1: call test_proc(1);
> >              ^
> > HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
> >
> > # call test_proc(1,2);
> >  b
> > ---
> >  1
> > (1 row)
> >
> > Looks weird that we have to pass in some (ignored?) values for output parameters.  In contrast, functions don't require this.
> >
> > CREATE FUNCTION test_func(a integer, out b integer)
> > LANGUAGE plpgsql
> > AS $$
> > BEGIN
> >   b := a;
> > END;
> > $$;
> >
> > # select  test_func(1);
> >  test_func
> > -----------
> >          1
> > (1 row)
> >
> > This makes me think we have an issue with stored procedures here.  I'll try to investigate it further.
>
> Oh, this seems to be intentional [1] and seems to be part of standard [2].
>
> Links
> 1. https://www.postgresql.org/docs/devel/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS-PROC
> 2. https://www.postgresql.org/message-id/2b8490fe-51af-e671-c504-47359dc453c5%402ndquadrant.com

The attached patchset contains patch 0001, which improves handling of
not in recovery state by usage of PromoteIsTriggered().  When
(PromoteIsTriggered() == false), last replay LSN is not accepted and
not reported in errdetail().

0002 contains patch finishing implicit transaction in default
isolation level REPEATABLE READ or higher with revised commit message.

------
Regards,
Alexander Korotkov
Supabase

  36. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Michael Paquier <michael@paquier.xyz> — 2024-11-04T06:04:09Z

    On Mon, Nov 04, 2024 at 06:29:42AM +0200, Alexander Korotkov wrote:
> The attached patchset contains patch 0001, which improves handling of
> not in recovery state by usage of PromoteIsTriggered().  When
> (PromoteIsTriggered() == false), last replay LSN is not accepted and
> not reported in errdetail().
> 
> 0002 contains patch finishing implicit transaction in default
> isolation level REPEATABLE READ or higher with revised commit message.

I was just catching up with this thread, and I'm still confused about
the state of things.  There are two things that are out of order for
me, at least, after skimming through the code on HEAD (I suspect there
is more):
- WaitForLSNReplay() uses an initial set of checks that are duplicated
in the main loop.  This is still overcomplicated, no?  Wouldn't it be
simpler to eliminate the first of checks or just have a goto block
with addLSNWaiter() called after the first round of checks?
- pg_wal_replay_wait_status() returns a status based on a static
variable that can only be accessed with the same backend as the one
that has called the wait function.  That's.. Err.. Unfriendly.  Why
being sticky with one backend for the job?

Using output parameters in a procedure is something I did not recall.
Based on your point about not using a function due your argument based
on the snapshots, let's just use that and forget about the status
function entirely (please?).

Based on the latest set of issues reported, it does not feel like this
is really baked and ready for prime day, either.  Perhaps it would be
less confusing to just revert the whole and repost a more complete and
structured implementation with an extra round of reviews?  There's
still time in this release cycle.
--
Michael

  37. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-04T14:02:08Z

    On Mon, Nov 4, 2024 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Nov 04, 2024 at 06:29:42AM +0200, Alexander Korotkov wrote:
> > The attached patchset contains patch 0001, which improves handling of
> > not in recovery state by usage of PromoteIsTriggered().  When
> > (PromoteIsTriggered() == false), last replay LSN is not accepted and
> > not reported in errdetail().
> >
> > 0002 contains patch finishing implicit transaction in default
> > isolation level REPEATABLE READ or higher with revised commit message.
>
> I was just catching up with this thread, and I'm still confused about
> the state of things.  There are two things that are out of order for
> me, at least, after skimming through the code on HEAD (I suspect there
> is more):
> - WaitForLSNReplay() uses an initial set of checks that are duplicated
> in the main loop.  This is still overcomplicated, no?  Wouldn't it be
> simpler to eliminate the first of checks or just have a goto block
> with addLSNWaiter() called after the first round of checks?
> - pg_wal_replay_wait_status() returns a status based on a static
> variable that can only be accessed with the same backend as the one
> that has called the wait function.  That's.. Err.. Unfriendly.  Why
> being sticky with one backend for the job?

That's a bit awkward, but I think generally valid.  Some backend can
wait for LSN, then the result could be read from the same backend.
Difference backends could wait for different LSNs with different
results, I definitely don't feel like something should be shared
across backends.

> Using output parameters in a procedure is something I did not recall.
> Based on your point about not using a function due your argument based
> on the snapshots, let's just use that and forget about the status
> function entirely (please?).

Please, check [1].  Usage of output parameters is a bit awkward too,
because you need to pass some value in there.  And more importantly,
usage of output parameters also causes snapshot problem, as it causes
another snapshot to be held.

I'm tending to think we don't necessarily need to have ability to get
the waiting status in the initial version of this feature.  The work
on this feature started in 2016.  It's already very long for a simple
feature of just waiting for LSN (simple in general design, while our
snapshot reality makes one full of trouble).  Having in-core
implementation for this seems like breakthrough already, even if it
doesn't have all the abilities it could potentially have.

> Based on the latest set of issues reported, it does not feel like this
> is really baked and ready for prime day, either.  Perhaps it would be
> less confusing to just revert the whole and repost a more complete and
> structured implementation with an extra round of reviews?  There's
> still time in this release cycle.

Yes, this makes sense.  Will revert.

Links.
1. https://www.postgresql.org/message-id/CAPpHfdvRmTzGJw5rQdSMkTxUPZkjwtbQ%3DLJE2u9Jqh9gFXHpmg%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase

  38. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-11-04T14:19:26Z

    On 2024-Nov-04, Alexander Korotkov wrote:

> On Mon, Nov 4, 2024 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:

> > Using output parameters in a procedure is something I did not recall.
> > Based on your point about not using a function due your argument based
> > on the snapshots, let's just use that and forget about the status
> > function entirely (please?).
> 
> Please, check [1].  Usage of output parameters is a bit awkward too,
> because you need to pass some value in there.  And more importantly,
> usage of output parameters also causes snapshot problem, as it causes
> another snapshot to be held.

I wonder if it would be better to go back to the original idea of using
special DDL syntax rather than a procedure.  It seems we've been piling
up hacks to get around the behavior of procedures, and we seem to have
grown one more to handle repeatable read transactions.

It's looking to me like there's just too much cruft in the quest to
avoid having to reach consensus on new syntax.  This might be a mistake.
Is it possible to backtrack on that decision?

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

  39. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Robert Haas <robertmhaas@gmail.com> — 2024-11-04T15:53:32Z

    On Mon, Nov 4, 2024 at 9:19 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> It's looking to me like there's just too much cruft in the quest to
> avoid having to reach consensus on new syntax.  This might be a mistake.
> Is it possible to backtrack on that decision?

There's also the patch that Heikki posted to wait using a
protocol-level facility. Maybe that's just a better fit and we don't
need either a procedure or new syntax.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  40. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Heikki Linnakangas <hlinnaka@iki.fi> — 2024-11-04T15:57:19Z

    On 04/11/2024 17:53, Robert Haas wrote:
> On Mon, Nov 4, 2024 at 9:19 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>> It's looking to me like there's just too much cruft in the quest to
>> avoid having to reach consensus on new syntax.  This might be a mistake.
>> Is it possible to backtrack on that decision?
> 
> There's also the patch that Heikki posted to wait using a
> protocol-level facility.

It was Peter E

> Maybe that's just a better fit and we don't need either a procedure
> or new syntax.
I think it would still be good to expose the feature at SQL level too. 
Makes it easier to test and makes it usable without client library 
changes, for example.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  41. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Robert Haas <robertmhaas@gmail.com> — 2024-11-04T16:53:20Z

    On Mon, Nov 4, 2024 at 10:57 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> > There's also the patch that Heikki posted to wait using a
> > protocol-level facility.
>
> It was Peter E

Oops.

> > Maybe that's just a better fit and we don't need either a procedure
> > or new syntax.
> I think it would still be good to expose the feature at SQL level too.
> Makes it easier to test and makes it usable without client library
> changes, for example.

OK. Well then I agree with Álvaro. If using a procedure isn't working
out nicely, it's better to consider alternatives than to force a round
peg into a square hole (with due regard for the fact that you can fit
anything through the square hole if you make the square hole big
enough...).

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  42. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Heikki Linnakangas <hlinnaka@iki.fi> — 2024-11-04T17:11:58Z

    On 04/11/2024 18:53, Robert Haas wrote:
> On Mon, Nov 4, 2024 at 10:57 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>>> Maybe that's just a better fit and we don't need either a procedure
>>> or new syntax.
>> I think it would still be good to expose the feature at SQL level too.
>> Makes it easier to test and makes it usable without client library
>> changes, for example.
> 
> OK. Well then I agree with Álvaro. If using a procedure isn't working
> out nicely, it's better to consider alternatives than to force a round
> peg into a square hole (with due regard for the fact that you can fit
> anything through the square hole if you make the square hole big
> enough...).

Agreed. Off the top of my, summary of issues I'm aware of:

- Missing documentation. The function itself is documented, but it's 
clearly intended for implementing read-after-write consistency in a 
cluster setup, so we should document how to do that.
- Not clear to me what the use case for the 'timeout' and 'noerror' 
arguments is. Why do we need it at all? (See first point on missing docs).
- Separate pg_wal_replay_wait_status() function to get result status is 
awkward
- Inconsistencies in behavior if server is not in recovery (Alexander's 
latest patch addresses this, but I didn't review it yet)
- pg_wal_replay_wait_status() is incorrectly marked as STABLE and 
PARALLEL SAFE
- Broken with default_transaction_isolation ='repeatable read'

Let's revert commit e546989a26 and rethink. I think the basic 
pg_wal_replay_wait() interface before e546989a26 was fine. Some of the 
above are issues above were present before e546989a26 already and need 
to be addressed in any case, but I believe they can be fixed without 
changing the interface.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  43. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-04T19:04:25Z

    On Mon, Nov 4, 2024 at 4:19 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2024-Nov-04, Alexander Korotkov wrote:
>
> > On Mon, Nov 4, 2024 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> > > Using output parameters in a procedure is something I did not recall.
> > > Based on your point about not using a function due your argument based
> > > on the snapshots, let's just use that and forget about the status
> > > function entirely (please?).
> >
> > Please, check [1].  Usage of output parameters is a bit awkward too,
> > because you need to pass some value in there.  And more importantly,
> > usage of output parameters also causes snapshot problem, as it causes
> > another snapshot to be held.
>
> I wonder if it would be better to go back to the original idea of using
> special DDL syntax rather than a procedure.  It seems we've been piling
> up hacks to get around the behavior of procedures, and we seem to have
> grown one more to handle repeatable read transactions.
>
> It's looking to me like there's just too much cruft in the quest to
> avoid having to reach consensus on new syntax.  This might be a mistake.
> Is it possible to backtrack on that decision?

Before committing pg_wal_replay_wait() I was under impression that
stored procedure would require the same amount of efforts than utility
statement to make backend "snapshot-less".  However, it appears that
if we have implicit REPEATABLE READ transaction, stored procedure
needs more efforts.  That makes the whole decision, at least,
questionable.

------
Regards,
Alexander Korotkov
Supabase

  44. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-04T21:00:23Z

    On Mon, Nov 4, 2024 at 8:04 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Mon, Nov 04, 2024 at 06:29:42AM +0200, Alexander Korotkov wrote:
> > The attached patchset contains patch 0001, which improves handling of
> > not in recovery state by usage of PromoteIsTriggered().  When
> > (PromoteIsTriggered() == false), last replay LSN is not accepted and
> > not reported in errdetail().
> >
> > 0002 contains patch finishing implicit transaction in default
> > isolation level REPEATABLE READ or higher with revised commit message.
>
> I was just catching up with this thread, and I'm still confused about
> the state of things.  There are two things that are out of order for
> me, at least, after skimming through the code on HEAD (I suspect there
> is more):
> - WaitForLSNReplay() uses an initial set of checks that are duplicated
> in the main loop.  This is still overcomplicated, no?  Wouldn't it be
> simpler to eliminate the first of checks or just have a goto block
> with addLSNWaiter() called after the first round of checks?
> - pg_wal_replay_wait_status() returns a status based on a static
> variable that can only be accessed with the same backend as the one
> that has called the wait function.  That's.. Err.. Unfriendly.  Why
> being sticky with one backend for the job?
>
> Using output parameters in a procedure is something I did not recall.
> Based on your point about not using a function due your argument based
> on the snapshots, let's just use that and forget about the status
> function entirely (please?).

On second thought, status function is probably not so silly idea.
Unlike result of utility statement/stored procedure, it could be used
inside another function, that is one may implement custom result
handling logic on postgres server side.  On contrast, if you get
something out of utility statement/stored procedure, it takes an extra
roundtrip to push it back to postgres backend.

> Based on the latest set of issues reported, it does not feel like this
> is really baked and ready for prime day, either.  Perhaps it would be
> less confusing to just revert the whole and repost a more complete and
> structured implementation with an extra round of reviews?  There's
> still time in this release cycle.

Reverted.

------
Regards,
Alexander Korotkov
Supabase

  45. Re: pgsql: Implement pg_wal_replay_wait() stored procedure

    Alexander Korotkov <aekorotkov@gmail.com> — 2024-11-04T21:05:00Z

    On Mon, Nov 4, 2024 at 5:57 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> On 04/11/2024 17:53, Robert Haas wrote:
> > On Mon, Nov 4, 2024 at 9:19 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> >> It's looking to me like there's just too much cruft in the quest to
> >> avoid having to reach consensus on new syntax.  This might be a mistake.
> >> Is it possible to backtrack on that decision?
> >
> > There's also the patch that Heikki posted to wait using a
> > protocol-level facility.
>
> It was Peter E
>
> > Maybe that's just a better fit and we don't need either a procedure
> > or new syntax.
> I think it would still be good to expose the feature at SQL level too.
> Makes it easier to test and makes it usable without client library
> changes, for example.

+1,
Also, it could potentially has wider use cases.  For example, waiting
for replay of not latest changes, but some intermediate changes.  Or
issuing pg_wal_replay_wait() from another process than one which made
the changes.

------
Regards,
Alexander Korotkov
Supabase