Horton

Re: pgsql: Implement pg_wal_replay_wait() stored procedure

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Peter Eisentraut <peter@eisentraut.org>, Alexander Korotkov <akorotkov@postgresql.org>, pgsql-committers@lists.postgresql.org
Date: 2024-09-27T14:07:09Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Revert pg_wal_replay_wait() stored procedure

  2. Add 'no_error' argument to pg_wal_replay_wait()

  3. Refactor WaitForLSNReplay() to return the result of waiting

  4. Make WaitForLSNReplay() issue FATAL on postmaster death

  5. Move LSN waiting declarations and definitions to better place

  6. Update oid for pg_wal_replay_wait() procedure

  7. Move pg_wal_replay_wait() to xlogfuncs.c

  8. 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