Re: pgsql: Implement pg_wal_replay_wait() stored procedure

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