Re: pgsql: Implement pg_wal_replay_wait() stored procedure

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)