Re: pgsql: Implement pg_wal_replay_wait() stored procedure

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