public inbox for [email protected]
help / color / mirror / Atom feedBug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
11+ messages / 4 participants
[nested] [flat]
* Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 02:03 SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-09 02:03 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
Hi hackers, Alexander,
An assertion failure (server crash in assert-enabled builds) occurs when
WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void
procedures.
Repro:
-- Run this on a standby
CREATE PROCEDURE test_wait()
LANGUAGE plpgsql AS $$
DECLARE
result text;
BEGIN
WAIT FOR LSN '0/1234' INTO result;
RAISE NOTICE '%', result;
END;
$$;
CALL test_wait();
The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that
requires a snapshot crashes the backend with:
TRAP: failed Assert("portal->portalSnapshot == NULL"),
File: "pquery.c", Line: 1776
Attached patches for both the test case and a potential fix. Please review.
Thanks,
Satya
Attachments:
[application/octet-stream] 0002-Add-regression-tests-for-WAIT-FOR-LSN-inside-PL-pgSQ.patch (2.1K, 3-0002-Add-regression-tests-for-WAIT-FOR-LSN-inside-PL-pgSQ.patch)
download | inline diff:
From d82c0f0d207537defadc25e8ef8f69cd3dc28b32 Mon Sep 17 00:00:00 2001
From: Satya Narlapuram <[email protected]>
Date: Thu, 9 Apr 2026 00:50:43 +0000
Subject: [PATCH v1 2/2] Add regression tests for WAIT FOR LSN inside PL/pgSQL
Add test section 7f to 049_wait_for_lsn.pl covering WAIT FOR LSN ...
INTO inside PL/pgSQL DO blocks and procedures.
---
src/test/recovery/t/049_wait_for_lsn.pl | 35 +++++++++++++++++++++++++
1 file changed, 35 insertions(+)
diff --git a/src/test/recovery/t/049_wait_for_lsn.pl b/src/test/recovery/t/049_wait_for_lsn.pl
index bf61b8c4..11d3611d 100644
--- a/src/test/recovery/t/049_wait_for_lsn.pl
+++ b/src/test/recovery/t/049_wait_for_lsn.pl
@@ -587,6 +587,41 @@ $output = $node_primary->safe_psql('postgres',
ok($output >= 0,
"multiple primary_flush waiters: primary flushed WAL up to target LSN");
+# Use an already-replayed LSN so the WAIT returns immediately on standby.
+my $wait_into_lsn = $node_standby->safe_psql('postgres',
+ "SELECT pg_last_wal_replay_lsn()");
+
+# 7f. Test DO block with WAIT FOR INTO followed by RAISE NOTICE on standby.
+$node_standby->safe_psql(
+ 'postgres', qq[
+ DO \$\$
+ DECLARE
+ result text;
+ BEGIN
+ WAIT FOR LSN '${wait_into_lsn}' INTO result;
+ RAISE NOTICE 'got: %', result;
+ END;
+ \$\$;]);
+ok(1, "WAIT FOR INTO in DO block does not crash on standby");
+
+# Test void procedure with WAIT FOR INTO followed by RAISE on standby.
+$node_primary->safe_psql(
+ 'postgres', qq[
+ CREATE PROCEDURE test_wait_into_raise()
+ LANGUAGE plpgsql AS \$\$
+ DECLARE
+ result text;
+ BEGIN
+ WAIT FOR LSN '${wait_into_lsn}' INTO result;
+ RAISE NOTICE 'wait result: %', result;
+ END;
+ \$\$;]);
+$node_primary->wait_for_catchup($node_standby);
+
+$node_standby->safe_psql('postgres',
+ "CALL test_wait_into_raise()");
+ok(1, "WAIT FOR INTO in void procedure does not crash on standby");
+
# 8. Check that the standby promotion terminates all standby wait modes. Start
# waiting for unreachable LSNs with standby_replay, standby_write, and
# standby_flush modes, then promote. Check the log for the relevant error
--
2.43.0
[application/octet-stream] 0001-Fix-assertion-failure-in-WAIT-FOR-LSN-when-used-insi.patch (2.4K, 4-0001-Fix-assertion-failure-in-WAIT-FOR-LSN-when-used-insi.patch)
download | inline diff:
From 16ace60f82062f0f13fc1814085b045aedd6f202 Mon Sep 17 00:00:00 2001
From: Satya Narlapuram <[email protected]>
Date: Thu, 9 Apr 2026 00:50:20 +0000
Subject: [PATCH v1 1/2] Fix assertion failure in WAIT FOR LSN when used inside
PL/pgSQL
WAIT FOR LSN ... INTO inside a DO block crashed with an assertion failure in
EnsurePortalSnapshotExists():
TRAP: failed Assert("portal->portalSnapshot == NULL"),
File: "pquery.c", Line: 1776
The root cause was that ExecWaitStmt() called PopActiveSnapshot() to
release the snapshot before waiting, but did not clear the active
portal's portalSnapshot pointer. When the next PL/pgSQL statement
requiring a snapshot called EnsurePortalSnapshotExists(), it found
a stale non-NULL portalSnapshot and hit the assertion.
PortalRunUtility() normally handles this cleanup in the top-level
execution path, but when WAIT FOR runs through SPI (PL/pgSQL),
ProcessUtility is called directly without the PortalRunUtility
wrapper, so the portalSnapshot was never cleared.
Fix by clearing ActivePortal->portalSnapshot after PopActiveSnapshot()
in ExecWaitStmt(), matching what PortalRunUtility() does.
---
src/backend/commands/wait.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/commands/wait.c b/src/backend/commands/wait.c
index 85fcd463..8d245dbd 100644
--- a/src/backend/commands/wait.c
+++ b/src/backend/commands/wait.c
@@ -24,6 +24,7 @@
#include "executor/executor.h"
#include "parser/parse_node.h"
#include "storage/proc.h"
+#include "tcop/pquery.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/pg_lsn.h"
@@ -146,10 +147,20 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
* PlannedStmtRequiresSnapshot(), even in an atomic context, CallStmt is
* processed with a snapshot. Thankfully, we can pop this snapshot,
* because PortalRunUtility() can tolerate this.
+ *
+ * When running inside PL/pgSQL, the active
+ * portal may hold a pointer to this snapshot in portalSnapshot. We must
+ * clear it so that EnsurePortalSnapshotExists() doesn't trip an assertion
+ * when the next PL/pgSQL statement tries to re-establish a snapshot.
*/
if (ActiveSnapshotSet())
+ {
PopActiveSnapshot();
+ if (ActivePortal && ActivePortal->portalSnapshot != NULL)
+ ActivePortal->portalSnapshot = NULL;
+ }
+
/*
* At second, invalidate a catalog snapshot if any. And we should be done
* with the preparation.
--
2.43.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 06:00 Alexander Korotkov <[email protected]>
parent: SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Alexander Korotkov @ 2026-04-09 06:00 UTC (permalink / raw)
To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; pgsql-hackers
Hi, Satya!
On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
> An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
>
> Repro:
>
> -- Run this on a standby
>
> CREATE PROCEDURE test_wait()
> LANGUAGE plpgsql AS $$
> DECLARE
> result text;
> BEGIN
> WAIT FOR LSN '0/1234' INTO result;
> RAISE NOTICE '%', result;
> END;
> $$;
> CALL test_wait();
>
>
> The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
>
> TRAP: failed Assert("portal->portalSnapshot == NULL"),
> File: "pquery.c", Line: 1776
>
> Attached patches for both the test case and a potential fix. Please review.
Thank you for reporting. But I doubt the fix is correct. Even that
this particular might work OK, I don't think it's safe to release
snapshots belonging to functions/procedures: it might affect them. I
tend to think we must forbid wrapping WAIT FOR LSN with
functions/procedures. I'll explore more on this today.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 06:38 Xuneng Zhou <[email protected]>
parent: Alexander Korotkov <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Xuneng Zhou @ 2026-04-09 06:38 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
On Thu, Apr 9, 2026 at 2:00 PM Alexander Korotkov <[email protected]> wrote:
>
> Hi, Satya!
>
> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
> > An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
> >
> > Repro:
> >
> > -- Run this on a standby
> >
> > CREATE PROCEDURE test_wait()
> > LANGUAGE plpgsql AS $$
> > DECLARE
> > result text;
> > BEGIN
> > WAIT FOR LSN '0/1234' INTO result;
> > RAISE NOTICE '%', result;
> > END;
> > $$;
> > CALL test_wait();
> >
> >
> > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
> >
> > TRAP: failed Assert("portal->portalSnapshot == NULL"),
> > File: "pquery.c", Line: 1776
> >
> > Attached patches for both the test case and a potential fix. Please review.
>
> Thank you for reporting. But I doubt the fix is correct. Even that
> this particular might work OK, I don't think it's safe to release
> snapshots belonging to functions/procedures: it might affect them. I
> tend to think we must forbid wrapping WAIT FOR LSN with
> functions/procedures. I'll explore more on this today.
>
--
Best,
Xuneng
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 07:04 Xuneng Zhou <[email protected]>
parent: Xuneng Zhou <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Xuneng Zhou @ 2026-04-09 07:04 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
Hi Satya, Alexander,
On Thu, Apr 9, 2026 at 2:38 PM Xuneng Zhou <[email protected]> wrote:
>
> On Thu, Apr 9, 2026 at 2:00 PM Alexander Korotkov <[email protected]> wrote:
> >
> > Hi, Satya!
> >
> > On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> > <[email protected]> wrote:
> > > An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
> > >
> > > Repro:
> > >
> > > -- Run this on a standby
> > >
> > > CREATE PROCEDURE test_wait()
> > > LANGUAGE plpgsql AS $$
> > > DECLARE
> > > result text;
> > > BEGIN
> > > WAIT FOR LSN '0/1234' INTO result;
> > > RAISE NOTICE '%', result;
> > > END;
> > > $$;
> > > CALL test_wait();
> > >
> > >
> > > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
> > >
> > > TRAP: failed Assert("portal->portalSnapshot == NULL"),
> > > File: "pquery.c", Line: 1776
> > >
> > > Attached patches for both the test case and a potential fix. Please review.
> >
> > Thank you for reporting. But I doubt the fix is correct. Even that
> > this particular might work OK, I don't think it's safe to release
> > snapshots belonging to functions/procedures: it might affect them. I
> > tend to think we must forbid wrapping WAIT FOR LSN with
> > functions/procedures. I'll explore more on this today.
> >
Opus, sorry for clicking send incidentally before typing anything...
I had looked at these patches before and didn’t see anything
particularly wrong except:
1. patch 1 unconditionally nulled ActivePortal->portalSnapshot
whenever it was non-NULL after the pop;
2. patch 2 used RAISE NOTICE after WAIT FOR, which seems not excercise
the bug straightforwardly.
I didn't realized the safety implications of releasing a procedure/DO
snapshot during PL execution. I’ve noticed several warnings in the
tree advising against this.
/*
* Ensure there's an active snapshot whilst we execute whatever's
* involved here. Note that this is *not* sufficient to make the
* world safe for TOAST pointers to be included in the returned data:
* the referenced data could have gone away while we didn't hold a
* snapshot. Hence, it's incumbent on PLs that can do COMMIT/ROLLBACK
* to not return TOAST pointers, unless those pointers were fetched
* after the last COMMIT/ROLLBACK in the procedure.
*
* XXX that is a really nasty, hard-to-test requirement. Is there a
* way to remove it?
*/
EnsurePortalSnapshotExists();
--
Best,
Xuneng
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 07:26 SATYANARAYANA NARLAPURAM <[email protected]>
parent: Alexander Korotkov <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-09 07:26 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; pgsql-hackers
Hi Alexnader,
On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <[email protected]>
wrote:
> Hi, Satya!
>
> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
> > An assertion failure (server crash in assert-enabled builds) occurs when
> WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void
> procedures.
> >
> > Repro:
> >
> > -- Run this on a standby
> >
> > CREATE PROCEDURE test_wait()
> > LANGUAGE plpgsql AS $$
> > DECLARE
> > result text;
> > BEGIN
> > WAIT FOR LSN '0/1234' INTO result;
> > RAISE NOTICE '%', result;
> > END;
> > $$;
> > CALL test_wait();
> >
> >
> > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that
> requires a snapshot crashes the backend with:
> >
> > TRAP: failed Assert("portal->portalSnapshot == NULL"),
> > File: "pquery.c", Line: 1776
> >
> > Attached patches for both the test case and a potential fix. Please
> review.
>
> Thank you for reporting. But I doubt the fix is correct. Even that
> this particular might work OK, I don't think it's safe to release
> snapshots belonging to functions/procedures: it might affect them. I
> tend to think we must forbid wrapping WAIT FOR LSN with
> functions/procedures. I'll explore more on this today.
Agreed, attached a v2 patch with your suggestion on preventing it running
from procedures.
Thanks,
Satya
Attachments:
[application/octet-stream] v2-0001-waitforlsn-forbid-in-functions.patch (1.8K, 3-v2-0001-waitforlsn-forbid-in-functions.patch)
download | inline diff:
diff --git a/src/backend/commands/wait.c b/src/backend/commands/wait.c
index 85fcd463..b84fa815 100644
--- a/src/backend/commands/wait.c
+++ b/src/backend/commands/wait.c
@@ -31,7 +31,8 @@
void
-ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
+ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, bool isTopLevel,
+ DestReceiver *dest)
{
XLogRecPtr lsn;
int64 timeout = 0;
@@ -135,6 +136,16 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
}
}
+ /*
+ * WAIT FOR must not run inside a function or procedure.
+ * Forbid this case upfront.
+ */
+ if (!isTopLevel)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s cannot be executed from a function or procedure",
+ "WAIT FOR")));
+
/*
* We are going to wait for the LSN. We should first care that we don't
* hold a snapshot and correspondingly our MyProc->xmin is invalid.
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 1d34c199..73a56f1d 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1062,7 +1062,8 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_WaitStmt:
{
- ExecWaitStmt(pstate, (WaitStmt *) parsetree, dest);
+ ExecWaitStmt(pstate, (WaitStmt *) parsetree, isTopLevel,
+ dest);
}
break;
diff --git a/src/include/commands/wait.h b/src/include/commands/wait.h
index 521a3129..d7b3ee85 100644
--- a/src/include/commands/wait.h
+++ b/src/include/commands/wait.h
@@ -16,7 +16,8 @@
#include "parser/parse_node.h"
#include "tcop/dest.h"
-extern void ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest);
+extern void ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, bool isTopLevel,
+ DestReceiver *dest);
extern TupleDesc WaitStmtResultDesc(WaitStmt *stmt);
#endif /* WAIT_H */
[application/octet-stream] v2-0001-waitforlsn-tests.patch (614B, 4-v2-0001-waitforlsn-tests.patch)
download | inline diff:
diff --git a/src/test/recovery/t/049_wait_for_lsn.pl b/src/test/recovery/t/049_wait_for_lsn.pl
index bf61b8c4..ca07873e 100644
--- a/src/test/recovery/t/049_wait_for_lsn.pl
+++ b/src/test/recovery/t/049_wait_for_lsn.pl
@@ -216,7 +216,7 @@ $node_standby->psql(
"SELECT pg_wal_replay_wait_wrap('${lsn3}');",
stderr => \$stderr);
ok( $stderr =~
- /WAIT FOR must be called without an active or registered snapshot/,
+ /WAIT FOR cannot be executed from a function or procedure/,
"get an error when running within another function");
# 6. Check parameter validation error cases on standby before promotion
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 11:47 Alexander Korotkov <[email protected]>
parent: Xuneng Zhou <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Alexander Korotkov @ 2026-04-09 11:47 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
On Thu, Apr 9, 2026 at 10:04 AM Xuneng Zhou <[email protected]> wrote:
> On Thu, Apr 9, 2026 at 2:38 PM Xuneng Zhou <[email protected]> wrote:
> >
> > On Thu, Apr 9, 2026 at 2:00 PM Alexander Korotkov <[email protected]> wrote:
> > >
> > > Hi, Satya!
> > >
> > > On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> > > <[email protected]> wrote:
> > > > An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
> > > >
> > > > Repro:
> > > >
> > > > -- Run this on a standby
> > > >
> > > > CREATE PROCEDURE test_wait()
> > > > LANGUAGE plpgsql AS $$
> > > > DECLARE
> > > > result text;
> > > > BEGIN
> > > > WAIT FOR LSN '0/1234' INTO result;
> > > > RAISE NOTICE '%', result;
> > > > END;
> > > > $$;
> > > > CALL test_wait();
> > > >
> > > >
> > > > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
> > > >
> > > > TRAP: failed Assert("portal->portalSnapshot == NULL"),
> > > > File: "pquery.c", Line: 1776
> > > >
> > > > Attached patches for both the test case and a potential fix. Please review.
> > >
> > > Thank you for reporting. But I doubt the fix is correct. Even that
> > > this particular might work OK, I don't think it's safe to release
> > > snapshots belonging to functions/procedures: it might affect them. I
> > > tend to think we must forbid wrapping WAIT FOR LSN with
> > > functions/procedures. I'll explore more on this today.
> > >
>
> Opus, sorry for clicking send incidentally before typing anything...
>
> I had looked at these patches before and didn’t see anything
> particularly wrong except:
> 1. patch 1 unconditionally nulled ActivePortal->portalSnapshot
> whenever it was non-NULL after the pop;
> 2. patch 2 used RAISE NOTICE after WAIT FOR, which seems not excercise
> the bug straightforwardly.
>
> I didn't realized the safety implications of releasing a procedure/DO
> snapshot during PL execution. I’ve noticed several warnings in the
> tree advising against this.
>
> /*
> * Ensure there's an active snapshot whilst we execute whatever's
> * involved here. Note that this is *not* sufficient to make the
> * world safe for TOAST pointers to be included in the returned data:
> * the referenced data could have gone away while we didn't hold a
> * snapshot. Hence, it's incumbent on PLs that can do COMMIT/ROLLBACK
> * to not return TOAST pointers, unless those pointers were fetched
> * after the last COMMIT/ROLLBACK in the procedure.
> *
> * XXX that is a really nasty, hard-to-test requirement. Is there a
> * way to remove it?
> */
> EnsurePortalSnapshotExists();
Regarding functions, function may be part of bigger query running with
particular snapshot. Note that single query is always executed within
the single snapshot (barring EvalPlanQual()) even in read committed
mode. Releasing a snapshot in the middle of the query could cause, if
even we somehow re-acquire a new snapshot, could cause rest of query
to be executed inconsistently with its beginning.
It is probably different for procedures, which don't have to stick to
a single snapshot. But I can still imagine WAIT FOR LSN to be inside
a loop over some query results or something similar. We are now past
FF for PG19. I suggest we should now forbid WAIT FOR LSN both in
functions and procedures. For PG20 we can reconsider some cases when
running WAIT FOR LSN inside the stored procedure is safe.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 12:28 Alexander Korotkov <[email protected]>
parent: SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Alexander Korotkov @ 2026-04-09 12:28 UTC (permalink / raw)
To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; pgsql-hackers
On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
> On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <[email protected]> wrote:
>> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
>> <[email protected]> wrote:
>> > An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
>> >
>> > Repro:
>> >
>> > -- Run this on a standby
>> >
>> > CREATE PROCEDURE test_wait()
>> > LANGUAGE plpgsql AS $$
>> > DECLARE
>> > result text;
>> > BEGIN
>> > WAIT FOR LSN '0/1234' INTO result;
>> > RAISE NOTICE '%', result;
>> > END;
>> > $$;
>> > CALL test_wait();
>> >
>> >
>> > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
>> >
>> > TRAP: failed Assert("portal->portalSnapshot == NULL"),
>> > File: "pquery.c", Line: 1776
>> >
>> > Attached patches for both the test case and a potential fix. Please review.
>>
>> Thank you for reporting. But I doubt the fix is correct. Even that
>> this particular might work OK, I don't think it's safe to release
>> snapshots belonging to functions/procedures: it might affect them. I
>> tend to think we must forbid wrapping WAIT FOR LSN with
>> functions/procedures. I'll explore more on this today.
>
>
> Agreed, attached a v2 patch with your suggestion on preventing it running
> from procedures.
Thank you. I've slightly revised your patch. I'm going to push it if
no objections.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v3-0001-Explicitly-forbid-WAIT-FOR-inside-functions-and-p.patch (5.2K, 2-v3-0001-Explicitly-forbid-WAIT-FOR-inside-functions-and-p.patch)
download | inline diff:
From 0d3b3a12357f65dc6ff0af54aabdaf88dbcd2a9b Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Thu, 9 Apr 2026 15:02:32 +0300
Subject: [PATCH v3] Explicitly forbid WAIT FOR inside functions and procedures
Previously we were relying on snapshot-based check to detect such cases.
However, it appears that when WAIT FOR is wrapped into a stored procedure
it could pass this check causing an error elsewhere. This commit implments
an explicit isTopLevel check to reject WAIT FOR when called from within
a function or procedure. The isTopLevel check catches this case early with
a clear error message, matching the pattern used by other utility commands
like VACUUM and REINDEX. The snapshot check is retained for the remaining
case: transactions with isolation level higher than READ COMMITTED.
Also add a test for WAIT FOR LSN wrapped in a procedure, complementing
the existing test that uses a function wrapper.
Reported-by: Satyanarayana Narlapuram <[email protected]>
Discussion: https://postgr.es/m/CAHg%2BQDcN-n3NUqgRtj%3DBQb9fFQmH8-DeEROCr%3DPDbw_BBRKOYA%40mail.gmail.com
Author: Satyanarayana Narlapuram <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
---
src/backend/commands/wait.c | 15 +++++++++++++--
src/backend/tcop/utility.c | 3 ++-
src/include/commands/wait.h | 3 ++-
src/test/recovery/t/049_wait_for_lsn.pl | 21 +++++++++++++++++++--
4 files changed, 36 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/wait.c b/src/backend/commands/wait.c
index 85fcd463b4c..ec132e125a5 100644
--- a/src/backend/commands/wait.c
+++ b/src/backend/commands/wait.c
@@ -31,7 +31,8 @@
void
-ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
+ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, bool isTopLevel,
+ DestReceiver *dest)
{
XLogRecPtr lsn;
int64 timeout = 0;
@@ -135,6 +136,16 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
}
}
+ /*
+ * WAIT FOR must not run inside a function or procedure. Forbid this case
+ * upfront.
+ */
+ if (!isTopLevel)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s cannot be executed from a function or procedure",
+ "WAIT FOR")));
+
/*
* We are going to wait for the LSN. We should first care that we don't
* hold a snapshot and correspondingly our MyProc->xmin is invalid.
@@ -161,7 +172,7 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("WAIT FOR must be called without an active or registered snapshot"),
- errdetail("WAIT FOR cannot be executed from a function or procedure, nor within a transaction with an isolation level higher than READ COMMITTED."));
+ errdetail("WAIT FOR cannot be executed within a transaction with an isolation level higher than READ COMMITTED."));
/*
* As the result we should hold no snapshot, and correspondingly our xmin
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 1d34c19913e..73a56f1df1d 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1062,7 +1062,8 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_WaitStmt:
{
- ExecWaitStmt(pstate, (WaitStmt *) parsetree, dest);
+ ExecWaitStmt(pstate, (WaitStmt *) parsetree, isTopLevel,
+ dest);
}
break;
diff --git a/src/include/commands/wait.h b/src/include/commands/wait.h
index 521a312908d..a563579695c 100644
--- a/src/include/commands/wait.h
+++ b/src/include/commands/wait.h
@@ -16,7 +16,8 @@
#include "parser/parse_node.h"
#include "tcop/dest.h"
-extern void ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest);
+extern void ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, bool isTopLevel,
+ DestReceiver *dest);
extern TupleDesc WaitStmtResultDesc(WaitStmt *stmt);
#endif /* WAIT_H */
diff --git a/src/test/recovery/t/049_wait_for_lsn.pl b/src/test/recovery/t/049_wait_for_lsn.pl
index bf61b8c47cf..e108301ccdb 100644
--- a/src/test/recovery/t/049_wait_for_lsn.pl
+++ b/src/test/recovery/t/049_wait_for_lsn.pl
@@ -215,10 +215,27 @@ $node_standby->psql(
'postgres',
"SELECT pg_wal_replay_wait_wrap('${lsn3}');",
stderr => \$stderr);
-ok( $stderr =~
- /WAIT FOR must be called without an active or registered snapshot/,
+ok($stderr =~ /WAIT FOR cannot be executed from a function or procedure/,
"get an error when running within another function");
+$node_primary->safe_psql(
+ 'postgres', qq[
+CREATE PROCEDURE pg_wal_replay_wait_proc(target_lsn pg_lsn) AS \$\$
+ BEGIN
+ EXECUTE format('WAIT FOR LSN %L;', target_lsn);
+ END
+\$\$
+LANGUAGE plpgsql;
+]);
+
+$node_primary->wait_for_catchup($node_standby);
+$node_standby->psql(
+ 'postgres',
+ "CALL pg_wal_replay_wait_proc('${lsn3}');",
+ stderr => \$stderr);
+ok($stderr =~ /WAIT FOR cannot be executed from a function or procedure/,
+ "get an error when running within a procedure");
+
# 6. Check parameter validation error cases on standby before promotion
my $test_lsn =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 21:37 SATYANARAYANA NARLAPURAM <[email protected]>
parent: Alexander Korotkov <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-09 21:37 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; pgsql-hackers
Hi Alexander,
On Thu, Apr 9, 2026 at 5:28 AM Alexander Korotkov <[email protected]>
wrote:
> On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
> > On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <[email protected]>
> wrote:
> >> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> >> <[email protected]> wrote:
> >> > An assertion failure (server crash in assert-enabled builds) occurs
> when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void
> procedures.
> >> >
> >> > Repro:
> >> >
> >> > -- Run this on a standby
> >> >
> >> > CREATE PROCEDURE test_wait()
> >> > LANGUAGE plpgsql AS $$
> >> > DECLARE
> >> > result text;
> >> > BEGIN
> >> > WAIT FOR LSN '0/1234' INTO result;
> >> > RAISE NOTICE '%', result;
> >> > END;
> >> > $$;
> >> > CALL test_wait();
> >> >
> >> >
> >> > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement
> that requires a snapshot crashes the backend with:
> >> >
> >> > TRAP: failed Assert("portal->portalSnapshot == NULL"),
> >> > File: "pquery.c", Line: 1776
> >> >
> >> > Attached patches for both the test case and a potential fix. Please
> review.
> >>
> >> Thank you for reporting. But I doubt the fix is correct. Even that
> >> this particular might work OK, I don't think it's safe to release
> >> snapshots belonging to functions/procedures: it might affect them. I
> >> tend to think we must forbid wrapping WAIT FOR LSN with
> >> functions/procedures. I'll explore more on this today.
> >
> >
> > Agreed, attached a v2 patch with your suggestion on preventing it running
> > from procedures.
>
> Thank you. I've slightly revised your patch. I'm going to push it if
> no objections.
>
LGTM, thank you!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re:Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-09 23:44 wang.xiao.peng <[email protected]>
parent: Alexander Korotkov <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: wang.xiao.peng @ 2026-04-09 23:44 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
At 2026-04-09 20:28:16, "Alexander Korotkov" <[email protected]> wrote:
>On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
><[email protected]> wrote:
>> On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <[email protected]> wrote:
>>> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
>>> <[email protected]> wrote:
>>> > An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
>>> >
>>> > Repro:
>>> >
>>> > -- Run this on a standby
>>> >
>>> > CREATE PROCEDURE test_wait()
>>> > LANGUAGE plpgsql AS $$
>>> > DECLARE
>>> > result text;
>>> > BEGIN
>>> > WAIT FOR LSN '0/1234' INTO result;
>>> > RAISE NOTICE '%', result;
>>> > END;
>>> > $$;
>>> > CALL test_wait();
>>> >
>>> >
>>> > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
>>> >
>>> > TRAP: failed Assert("portal->portalSnapshot == NULL"),
>>> > File: "pquery.c", Line: 1776
>>> >
>>> > Attached patches for both the test case and a potential fix. Please review.
>>>
>>> Thank you for reporting. But I doubt the fix is correct. Even that
>>> this particular might work OK, I don't think it's safe to release
>>> snapshots belonging to functions/procedures: it might affect them. I
>>> tend to think we must forbid wrapping WAIT FOR LSN with
>>> functions/procedures. I'll explore more on this today.
>>
>>
>> Agreed, attached a v2 patch with your suggestion on preventing it running
>> from procedures.
>
>Thank you. I've slightly revised your patch. I'm going to push it if
>no objections.
>
>------
>Regards,
>Alexander Korotkov
>Supabase
Hi,
This patch looks good to me overall. I spotted a typo in the commit message:
"it could pass this check causing an error elsewhere. This commit implments"
implments -> implements, missing an "e".
Regard,
Xiaopeng Wang
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-10 07:36 Xuneng Zhou <[email protected]>
parent: wang.xiao.peng <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Xuneng Zhou @ 2026-04-10 07:36 UTC (permalink / raw)
To: wang.xiao.peng <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
On Fri, Apr 10, 2026 at 7:45 AM wang.xiao.peng <[email protected]> wrote:
>
>
>
>
>
>
>
>
> At 2026-04-09 20:28:16, "Alexander Korotkov" <[email protected]> wrote:
> >On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
> ><[email protected]> wrote:
> >> On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <[email protected]> wrote:
> >>> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> >>> <[email protected]> wrote:
> >>> > An assertion failure (server crash in assert-enabled builds) occurs when WAIT FOR LSN ... INTO is used inside PL/pgSQL DO blocks or within void procedures.
> >>> >
> >>> > Repro:
> >>> >
> >>> > -- Run this on a standby
> >>> >
> >>> > CREATE PROCEDURE test_wait()
> >>> > LANGUAGE plpgsql AS $$
> >>> > DECLARE
> >>> > result text;
> >>> > BEGIN
> >>> > WAIT FOR LSN '0/1234' INTO result;
> >>> > RAISE NOTICE '%', result;
> >>> > END;
> >>> > $$;
> >>> > CALL test_wait();
> >>> >
> >>> >
> >>> > The WAIT FOR itself succeeds, but the very next PL/pgSQL statement that requires a snapshot crashes the backend with:
> >>> >
> >>> > TRAP: failed Assert("portal->portalSnapshot == NULL"),
> >>> > File: "pquery.c", Line: 1776
> >>> >
> >>> > Attached patches for both the test case and a potential fix. Please review.
> >>>
> >>> Thank you for reporting. But I doubt the fix is correct. Even that
> >>> this particular might work OK, I don't think it's safe to release
> >>> snapshots belonging to functions/procedures: it might affect them. I
> >>> tend to think we must forbid wrapping WAIT FOR LSN with
> >>> functions/procedures. I'll explore more on this today.
> >>
> >>
> >> Agreed, attached a v2 patch with your suggestion on preventing it running
> >> from procedures.
> >
> >Thank you. I've slightly revised your patch. I'm going to push it if
> >no objections.
> >
> >------
> >Regards,
> >Alexander Korotkov
> >Supabase
>
> Hi,
> This patch looks good to me overall. I spotted a typo in the commit message:
>
> "it could pass this check causing an error elsewhere. This commit implments"
>
> implments -> implements, missing an "e".
>
I’ve revised the patch. Moving the non–top-level rejection to the
beginning of the function may help avoid unnecessary parsing and
validation work, although it could make the reasoning slightly less
localized.
Since this is user-facing, should we explicitly document this
constraint to make the behavior less surprising? The rejection applies
not only to wrapping the command in a procedure or function, but also
within a DO block.
It might also be worth adding a regression test and refining the error
message accordingly. With this new constraint, some existing comments
were outdated and have been updated as well.
--
Best,
Xuneng
Attachments:
[application/x-patch] v4-0001-Explicitly-forbid-non-top-level-WAIT-FOR-executio.patch (7.1K, 2-v4-0001-Explicitly-forbid-non-top-level-WAIT-FOR-executio.patch)
download | inline diff:
From c98351d28b158fc0b34e60a16639d35e7f358f72 Mon Sep 17 00:00:00 2001
From: alterego655 <[email protected]>
Date: Fri, 10 Apr 2026 14:46:32 +0800
Subject: [PATCH v4] Explicitly forbid non-top-level WAIT FOR execution
Previously we were relying on a snapshot-based check to detect invalid
execution contexts. However, when WAIT FOR is wrapped into a stored
procedure or a DO block, it could pass this check, causing an error
elsewhere.
This commit implements an explicit isTopLevel check to reject WAIT FOR
when called from within a function, procedure, or DO block. The
isTopLevel check catches these cases early with a clear error message,
matching the pattern used by other utility commands like VACUUM and
REINDEX. The snapshot check is retained for the remaining case:
top-level execution within a transaction block using an isolation level
higher than READ COMMITTED.
Also adds tests for WAIT FOR LSN wrapped in a procedure and DO block,
complementing the existing test that uses a function wrapper.
Reported-by: Satyanarayana Narlapuram <[email protected]>
Discussion: https://postgr.es/m/CAHg%2BQDcN-n3NUqgRtj%3DBQb9fFQmH8-DeEROCr%3DPDbw_BBRKOYA%40mail.gmail.com
Author: Satyanarayana Narlapuram <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
---
doc/src/sgml/ref/wait_for.sgml | 8 +++++++
src/backend/commands/wait.c | 22 +++++++++++++-----
src/backend/tcop/utility.c | 3 ++-
src/include/commands/wait.h | 3 ++-
src/test/recovery/t/049_wait_for_lsn.pl | 30 ++++++++++++++++++++++---
5 files changed, 55 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ref/wait_for.sgml b/doc/src/sgml/ref/wait_for.sgml
index c30fba6f05a..9ba785ea321 100644
--- a/doc/src/sgml/ref/wait_for.sgml
+++ b/doc/src/sgml/ref/wait_for.sgml
@@ -221,6 +221,14 @@ WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
<refsect1>
<title>Notes</title>
+ <para>
+ <command>WAIT FOR</command> must be executed as a top-level command.
+ It cannot be executed from a function, procedure, or
+ <command>DO</command> block. It also requires that no active or
+ registered snapshot be held, and therefore cannot be used in contexts
+ where such a snapshot must remain active, including transactions running
+ at isolation levels higher than <literal>READ COMMITTED</literal>.
+ </para>
<para>
<command>WAIT FOR</command> waits until the specified
diff --git a/src/backend/commands/wait.c b/src/backend/commands/wait.c
index 85fcd463b4c..12888281d9b 100644
--- a/src/backend/commands/wait.c
+++ b/src/backend/commands/wait.c
@@ -31,7 +31,8 @@
void
-ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
+ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, bool isTopLevel,
+ DestReceiver *dest)
{
XLogRecPtr lsn;
int64 timeout = 0;
@@ -45,6 +46,16 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
bool no_throw_specified = false;
bool mode_specified = false;
+ /*
+ * WAIT FOR must not be run as a non-top-level statement (e.g., inside a
+ * function, procedure, or DO block). Forbid this case upfront.
+ */
+ if (!isTopLevel)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s can only be executed as a top-level statement",
+ "WAIT FOR")));
+
/* Parse and validate the mandatory LSN */
lsn = DatumGetLSN(DirectFunctionCall1(pg_lsn_in,
CStringGetDatum(stmt->lsn_literal)));
@@ -142,10 +153,9 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
* implying a kind of self-deadlock. This is the reason why WAIT FOR is a
* command, not a procedure or function.
*
- * At first, we should check there is no active snapshot. According to
- * PlannedStmtRequiresSnapshot(), even in an atomic context, CallStmt is
- * processed with a snapshot. Thankfully, we can pop this snapshot,
- * because PortalRunUtility() can tolerate this.
+ * Non-top-level contexts are rejected above, but be defensive and pop
+ * any active snapshot if one is present. PortalRunUtility() can
+ * tolerate utility commands that remove the active snapshot.
*/
if (ActiveSnapshotSet())
PopActiveSnapshot();
@@ -161,7 +171,7 @@ ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest)
ereport(ERROR,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("WAIT FOR must be called without an active or registered snapshot"),
- errdetail("WAIT FOR cannot be executed from a function or procedure, nor within a transaction with an isolation level higher than READ COMMITTED."));
+ errdetail("WAIT FOR cannot be executed within a transaction with an isolation level higher than READ COMMITTED."));
/*
* As the result we should hold no snapshot, and correspondingly our xmin
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 1d34c19913e..73a56f1df1d 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1062,7 +1062,8 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_WaitStmt:
{
- ExecWaitStmt(pstate, (WaitStmt *) parsetree, dest);
+ ExecWaitStmt(pstate, (WaitStmt *) parsetree, isTopLevel,
+ dest);
}
break;
diff --git a/src/include/commands/wait.h b/src/include/commands/wait.h
index 521a312908d..a563579695c 100644
--- a/src/include/commands/wait.h
+++ b/src/include/commands/wait.h
@@ -16,7 +16,8 @@
#include "parser/parse_node.h"
#include "tcop/dest.h"
-extern void ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, DestReceiver *dest);
+extern void ExecWaitStmt(ParseState *pstate, WaitStmt *stmt, bool isTopLevel,
+ DestReceiver *dest);
extern TupleDesc WaitStmtResultDesc(WaitStmt *stmt);
#endif /* WAIT_H */
diff --git a/src/test/recovery/t/049_wait_for_lsn.pl b/src/test/recovery/t/049_wait_for_lsn.pl
index bf61b8c47cf..8358c57f7b7 100644
--- a/src/test/recovery/t/049_wait_for_lsn.pl
+++ b/src/test/recovery/t/049_wait_for_lsn.pl
@@ -215,9 +215,33 @@ $node_standby->psql(
'postgres',
"SELECT pg_wal_replay_wait_wrap('${lsn3}');",
stderr => \$stderr);
-ok( $stderr =~
- /WAIT FOR must be called without an active or registered snapshot/,
- "get an error when running within another function");
+ok($stderr =~ /WAIT FOR can only be executed as a top-level statement/,
+ "get an error when running within a function");
+
+$node_primary->safe_psql(
+ 'postgres', qq[
+CREATE PROCEDURE pg_wal_replay_wait_proc(target_lsn pg_lsn) AS \$\$
+ BEGIN
+ EXECUTE format('WAIT FOR LSN %L;', target_lsn);
+ END
+\$\$
+LANGUAGE plpgsql;
+]);
+
+$node_primary->wait_for_catchup($node_standby);
+$node_standby->psql(
+ 'postgres',
+ "CALL pg_wal_replay_wait_proc('${lsn3}');",
+ stderr => \$stderr);
+ok($stderr =~ /WAIT FOR can only be executed as a top-level statement/,
+ "get an error when running within a procedure");
+
+$node_standby->psql(
+ 'postgres',
+ "DO \$\$ BEGIN EXECUTE format('WAIT FOR LSN %L;', '${lsn3}'); END \$\$;",
+ stderr => \$stderr);
+ok($stderr =~ /WAIT FOR can only be executed as a top-level statement/,
+ "get an error when running within a DO block");
# 6. Check parameter validation error cases on standby before promotion
my $test_lsn =
--
2.51.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures
@ 2026-04-13 11:06 Alexander Korotkov <[email protected]>
parent: Xuneng Zhou <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Alexander Korotkov @ 2026-04-13 11:06 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: wang.xiao.peng <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]>; PostgreSQL Hackers <[email protected]>; pgsql-hackers
On Fri, Apr 10, 2026 at 10:36 AM Xuneng Zhou <[email protected]> wrote:
> I’ve revised the patch. Moving the non–top-level rejection to the
> beginning of the function may help avoid unnecessary parsing and
> validation work, although it could make the reasoning slightly less
> localized.
>
> Since this is user-facing, should we explicitly document this
> constraint to make the behavior less surprising? The rejection applies
> not only to wrapping the command in a procedure or function, but also
> within a DO block.
>
> It might also be worth adding a regression test and refining the error
> message accordingly. With this new constraint, some existing comments
> were outdated and have been updated as well.
Accepted, thank you. Also, I've added errdetail() to clarify when the
statement could be not top-level. Pushed.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-04-13 11:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-09 02:03 Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-09 06:00 ` Alexander Korotkov <[email protected]>
2026-04-09 06:38 ` Xuneng Zhou <[email protected]>
2026-04-09 07:04 ` Xuneng Zhou <[email protected]>
2026-04-09 11:47 ` Alexander Korotkov <[email protected]>
2026-04-09 07:26 ` SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-09 12:28 ` Alexander Korotkov <[email protected]>
2026-04-09 21:37 ` SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-09 23:44 ` wang.xiao.peng <[email protected]>
2026-04-10 07:36 ` Xuneng Zhou <[email protected]>
2026-04-13 11:06 ` Alexander Korotkov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox