Re: pgsql: Implement pg_wal_replay_wait() stored procedure

On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinnaka(at)iki(dot)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