On Sun, Nov 3, 2024 at 11:03 PM Alexander Korotkov <aekorot...@gmail.com> wrote: > On Sun, Nov 3, 2024 at 10:54 PM Alexander Korotkov <aekorot...@gmail.com> > wrote: > > > > On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorot...@gmail.com> > > wrote: > > > > > > On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinn...@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
v2-0002-Teach-pg_wal_replay_wait-to-handle-REPEATABLE-REA.patch
Description: Binary data
v2-0001-pg_wal_replay_wait-Improve-handling-of-standby-pr.patch
Description: Binary data
