Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Explicitly forbid non-top-level WAIT FOR execution

  1. Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-09T02:03:31Z

    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

  2. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-04-09T06:00:06Z

    Hi, Satya!

On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> 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

  3. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Xuneng Zhou <xunengzhou@gmail.com> — 2026-04-09T06:38:59Z

    On Thu, Apr 9, 2026 at 2:00 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> Hi, Satya!
>
> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> 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

  4. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Xuneng Zhou <xunengzhou@gmail.com> — 2026-04-09T07:04:08Z

    Hi Satya, Alexander,

On Thu, Apr 9, 2026 at 2:38 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:
>
> On Thu, Apr 9, 2026 at 2:00 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > Hi, Satya!
> >
> > On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> > <satyanarlapuram@gmail.com> 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

  5. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-09T07:26:54Z

    Hi Alexnader,

On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <aekorotkov@gmail.com>
wrote:

> Hi, Satya!
>
> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> 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

  6. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-04-09T11:47:05Z

    On Thu, Apr 9, 2026 at 10:04 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:
> On Thu, Apr 9, 2026 at 2:38 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:
> >
> > On Thu, Apr 9, 2026 at 2:00 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > >
> > > Hi, Satya!
> > >
> > > On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> > > <satyanarlapuram@gmail.com> 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

  7. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-04-09T12:28:16Z

    On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
> On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
>> <satyanarlapuram@gmail.com> 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

  8. Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-09T21:37:44Z

    Hi Alexander,

On Thu, Apr 9, 2026 at 5:28 AM Alexander Korotkov <aekorotkov@gmail.com>
wrote:

> On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> > On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <aekorotkov@gmail.com>
> wrote:
> >> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> >> <satyanarlapuram@gmail.com> 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!

  9. Re:Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Xiaopeng Wang <wxp_728@163.com> — 2026-04-09T23:44:54Z

    











At 2026-04-09 20:28:16, "Alexander Korotkov" <aekorotkov@gmail.com> wrote:
>On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
><satyanarlapuram@gmail.com> wrote:
>> On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>>> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
>>> <satyanarlapuram@gmail.com> 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

  10. Re: Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Xuneng Zhou <xunengzhou@gmail.com> — 2026-04-10T07:36:04Z

    On Fri, Apr 10, 2026 at 7:45 AM wang.xiao.peng <wxp_728@163.com> wrote:
>
>
>
>
>
>
>
>
> At 2026-04-09 20:28:16, "Alexander Korotkov" <aekorotkov@gmail.com> wrote:
> >On Thu, Apr 9, 2026 at 10:27 AM SATYANARAYANA NARLAPURAM
> ><satyanarlapuram@gmail.com> wrote:
> >> On Wed, Apr 8, 2026 at 11:00 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >>> On Thu, Apr 9, 2026 at 5:03 AM SATYANARAYANA NARLAPURAM
> >>> <satyanarlapuram@gmail.com> 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

  11. Re: Re: Bug: WAIT FOR LSN crashes with assertion failure inside PL/pgSQL DO blocks and procedures

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-04-13T11:06:38Z

    On Fri, Apr 10, 2026 at 10:36 AM Xuneng Zhou <xunengzhou@gmail.com> 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