Horton

Re: pgsql: Implement pg_wal_replay_wait() stored procedure

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Pavel Borisov <pashkin.elfe@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, Michael Paquier <michael@paquier.xyz>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-11-04T04:29:42Z
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

Attachments

On Sun, Nov 3, 2024 at 11:03 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> On Sun, Nov 3, 2024 at 10:54 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > >
> > > On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinnaka@iki.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.
> >
> > I've rechecked the output parameters for stored procedures.  And I think the behavior I previously discovered is an anomaly.
> >
> > CREATE PROCEDURE test_proc(a integer, out b integer)
> > LANGUAGE plpgsql
> > AS $$
> > BEGIN
> >   b := a;
> > END;
> > $$;
> >
> > # call test_proc(1);
> > ERROR:  procedure test_proc(integer) does not exist
> > LINE 1: call test_proc(1);
> >              ^
> > HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
> >
> > # call test_proc(1,2);
> >  b
> > ---
> >  1
> > (1 row)
> >
> > Looks weird that we have to pass in some (ignored?) values for output parameters.  In contrast, functions don't require this.
> >
> > CREATE FUNCTION test_func(a integer, out b integer)
> > LANGUAGE plpgsql
> > AS $$
> > BEGIN
> >   b := a;
> > END;
> > $$;
> >
> > # select  test_func(1);
> >  test_func
> > -----------
> >          1
> > (1 row)
> >
> > This makes me think we have an issue with stored procedures here.  I'll try to investigate it further.
>
> Oh, this seems to be intentional [1] and seems to be part of standard [2].
>
> Links
> 1. https://www.postgresql.org/docs/devel/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS-PROC
> 2. https://www.postgresql.org/message-id/2b8490fe-51af-e671-c504-47359dc453c5%402ndquadrant.com

The attached patchset contains patch 0001, which improves handling of
not in recovery state by usage of PromoteIsTriggered().  When
(PromoteIsTriggered() == false), last replay LSN is not accepted and
not reported in errdetail().

0002 contains patch finishing implicit transaction in default
isolation level REPEATABLE READ or higher with revised commit message.

------
Regards,
Alexander Korotkov
Supabase