Re: pgsql: Implement pg_wal_replay_wait() stored procedure

On Thu, Sep 19, 2024 at 3:47 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> On Tue, Sep 3, 2024 at 4:07 PM Alexander Korotkov <aekorotkov(at)gmail(dot)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(at)postgres=# CALL pg_wal_replay_wait('0/0'::pg_lsn);
> CALL
> Time: 2.061 ms
> smagen(at)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