Re: pgsql: Implement pg_wal_replay_wait() stored procedure

On Sun, Nov 3, 2024 at 11:03 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> On Sun, Nov 3, 2024 at 10:54 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> >
> > On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> > >
> > > 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.
> >
> > 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