Re: pgsql: Implement pg_wal_replay_wait() stored procedure

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

------
Regards,
Alexander Korotkov
Supabase