BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice

BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice

The following bug has been logged on the website:

Bug reference:      19493
Logged by:          Nikita Kalinin
Email address:      n.kalinin@postgrespro.ru
PostgreSQL version: 18.4
Operating system:   Fedora 44
Description:        

Hi,
I found an assertion failure in pg_plan_advice.
Reproducer:

LOAD 'pg_plan_advice';

CREATE TABLE a(i int);
CREATE TABLE b(i int);

SET pg_plan_advice.feedback_warnings = on;
SET pg_plan_advice.advice = 'DO_NOT_SCAN((a))';

EXPLAIN
SELECT *
FROM a
WHERE EXISTS (
    SELECT 1
    FROM b
    WHERE b.i = a.i
);

Result:
2026-05-26 21:36:46.452 +07 [83331] LOG:  database system is ready to accept
connections
TRAP: failed Assert("target->ttype == PGPA_TARGET_IDENTIFIER"), File:
"pgpa_trove.c", Line: 182, PID: 83390
postgres: nkpit postgres [local] EXPLAIN(ExceptionalCondition+0x57)
[0xa2d077]
/tmp/pg/lib/postgresql/pg_plan_advice.so(+0xb0b2) [0x7fb2c06440b2]
/tmp/pg/lib/postgresql/pg_plan_advice.so(+0x533f) [0x7fb2c063e33f]
postgres: nkpit postgres [local] EXPLAIN(standard_planner+0x1ff) [0x7b51ff]
...
Backtrace:
#0  __pthread_kill_implementation (threadid=,
signo=signo@entry=6,
    no_tid=no_tid@entry=0) at pthread_kill.c:44
#1  0x00007fb2bf27a8d3 in __pthread_kill_internal (threadid=,
signo=6)
    at pthread_kill.c:89
#2  0x00007fb2bf21f48e in __GI_raise (sig=sig@entry=6) at
../sysdeps/posix/raise.c:26
#3  0x00007fb2bf2067b3 in __GI_abort () at abort.c:77
#4  0x0000000000a2d098 in ExceptionalCondition (
    conditionName=conditionName@entry=0x7fb2c0649828 "target->ttype ==
PGPA_TARGET_IDENTIFIER",
    fileName=fileName@entry=0x7fb2c064a184 "pgpa_trove.c",
lineNumber=lineNumber@entry=182)
    at assert.c:65
#5  0x00007fb2c06440b2 in pgpa_build_trove (advice_items=0x32a59488) at
pgpa_trove.c:182
#6  0x00007fb2c063e33f in pgpa_planner_setup (glob=0x32985888,
parse=0x32956d60,
    query_string=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT
1 FROM b WHERE b.i = a.i);", cursorOptions=2048,
tuple_fraction=0x7ffe98a152f0, es=0x32985308) at pgpa_planner.c:255
#7  0x00000000007b51ff in standard_planner (parse=0x32956d60,
query_string=,
    cursorOptions=2048, boundParams=, es=0x32985308) at
planner.c:533
#8  0x00000000007b5a0d in planner (parse=parse@entry=0x32956d60,
    query_string=query_string@entry=0x329558b0 "EXPLAIN SELECT *\nFROM
a\nWHERE EXISTS (SELECT 1 FROM b WHERE b.i = a.i);",
cursorOptions=cursorOptions@entry=2048,
    boundParams=boundParams@entry=0x0, es=es@entry=0x32985308) at
planner.c:342
#9  0x00000000008c297d in pg_plan_query
(querytree=querytree@entry=0x32956d60,
    query_string=query_string@entry=0x329558b0 "EXPLAIN SELECT *\nFROM
a\nWHERE EXISTS (SELECT 1 FROM b WHERE b.i = a.i);", cursorOptions=2048,
boundParams=boundParams@entry=0x0,
    es=es@entry=0x32985308) at postgres.c:917
#10 0x000000000062ed1e in standard_ExplainOneQuery (query=0x32956d60,
    cursorOptions=, into=0x0, es=0x32985308,
    queryString=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT 1
FROM b WHERE b.i = a.i);", params=0x0, queryEnv=0x0) at explain.c:359
#11 0x000000000062ef8e in ExplainOneQuery (query=,
cursorOptions=,
    into=, es=, pstate=,
params=)
    at explain.c:315
--Type  for more, q to quit, c to continue without paging--c
#12 0x000000000062f0ae in ExplainQuery (pstate=0x32982990, stmt=0x32956ba0,
params=0x0,
    dest=0x32985278) at ../../../src/include/nodes/nodes.h:178
#13 0x00000000008c8819 in standard_ProcessUtility (pstmt=0x32956c50,
    queryString=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT 1
FROM b WHERE b.i = a.i);", readOnlyTree=,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
    dest=0x32985278, qc=0x7ffe98a15660) at utility.c:871
#14 0x00000000008c6b8d in PortalRunUtility (portal=portal@entry=0x329f91e0,
pstmt=0x32956c50,
    isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=true,
    dest=dest@entry=0x32985278, qc=qc@entry=0x7ffe98a15660) at pquery.c:1149
#15 0x00000000008c7060 in FillPortalStore (portal=portal@entry=0x329f91e0,
    isTopLevel=isTopLevel@entry=true) at
../../../src/include/nodes/nodes.h:178
#16 0x00000000008c737d in PortalRun (portal=portal@entry=0x329f91e0,
    count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
    dest=dest@entry=0x32a690d8, altdest=altdest@entry=0x32a690d8,
qc=qc@entry=0x7ffe98a15830)
    at pquery.c:756
#17 0x00000000008c2f48 in exec_simple_query (
    query_string=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT
1 FROM b WHERE b.i = a.i);") at postgres.c:1290
#18 0x00000000008c4a21 in PostgresMain (dbname=,
username=)
    at postgres.c:4856
#19 0x00000000008bea1d in BackendMain (startup_data=,
    startup_data_len=) at backend_startup.c:124
#20 0x00000000007fea2e in postmaster_child_launch (child_type=, child_slot=1,
    startup_data=startup_data@entry=0x7ffe98a15c80,
startup_data_len=startup_data_len@entry=24,
    client_sock=client_sock@entry=0x7ffe98a15ca0) at launch_backend.c:268
#21 0x0000000000802436 in BackendStartup (client_sock=0x7ffe98a15ca0) at
postmaster.c:3627
#22 ServerLoop () at postmaster.c:1728
#23 0x0000000000803ef9 in PostmasterMain (argc=argc@entry=3,
argv=argv@entry=0x328ff080)
    at postmaster.c:1415
#24 0x00000000004a1a68 in main (argc=3, argv=0x328ff080) at main.c:231

Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice

Hello,

I am not sure pg_plan_advice is supported with PG 18.4 ?

I cannot reproduce with latest commit from PG 19 master branch:

$ git log -n 1
commit 61ea5cc6a61ff9eb8b3d7b055e507a726e5856c7 (HEAD -> master, 
origin/master, origin/HEAD)
Author: Tom Lane 
Date:   Tue May 26 11:58:25 2026 -0400

     Add stack depth check to QueueFKConstraintValidation().

     QueueFKConstraintValidation() recurses through the partition hierarchy
     to queue child constraint validations and to mark child rows as
     validated.  With a sufficiently deep partition tree, this can result
     in a stack-overflow crash.  Defend against that as we do elsewhere.

     Bug: #19482
     Reported-by: Alexander Lakhin 
     Author: Ayush Tiwari 
     Reviewed-by: Tom Lane 
     Discussion: https://postgr.es/m/19482-4cc37cbf52d55235@postgresql.org
     Backpatch-through: 18

postgres=# select version();
                                                    version
--------------------------------------------------------------------------------------------------------------
  PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
11.5.0 20240719 (Red Hat 11.5.0-11), 64-bit
(1 row)

postgres=#

I tested with:

DROP TABLE a;
DROP TABLE
DROP TABLE b;
DROP TABLE
LOAD 'pg_plan_advice';
LOAD
CREATE TABLE a(i int);
CREATE TABLE
CREATE TABLE b(i int);
CREATE TABLE
SET pg_plan_advice.feedback_warnings = on;
SET
SET pg_plan_advice.advice = 'DO_NOT_SCAN((a))';
SET
EXPLAIN
SELECT *
FROM a
WHERE EXISTS (
     SELECT 1
     FROM b
     WHERE b.i = a.i
);
psql:bug.sql:19: WARNING:  supplied plan advice was not enforced
DETAIL:  advice DO_NOT_SCAN((a)) feedback is "matched, failed"
                               QUERY PLAN
-----------------------------------------------------------------------
  Hash Join  (cost=46.38..102.75 rows=1275 width=4)
    Hash Cond: (a.i = b.i)
    ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=4)
          Disabled: true
    ->  Hash  (cost=43.88..43.88 rows=200 width=4)
          ->  HashAggregate  (cost=41.88..43.88 rows=200 width=4)
                Group Key: b.i
                ->  Seq Scan on b  (cost=0.00..35.50 rows=2550 width=4)
  Supplied Plan Advice:
    DO_NOT_SCAN((a)) /* matched, failed */
(10 rows)


Le 26/05/2026 à 16:44, PG Bug reporting form a écrit :
> The following bug has been logged on the website:
>
> Bug reference:      19493
> Logged by:          Nikita Kalinin
> Email address:      n.kalinin@postgrespro.ru
> PostgreSQL version: 18.4
> Operating system:   Fedora 44
> Description:
>
> Hi,
> I found an assertion failure in pg_plan_advice.
> Reproducer:
>
> LOAD 'pg_plan_advice';
>
> CREATE TABLE a(i int);
> CREATE TABLE b(i int);
>
> SET pg_plan_advice.feedback_warnings = on;
> SET pg_plan_advice.advice = 'DO_NOT_SCAN((a))';
>
> EXPLAIN
> SELECT *
> FROM a
> WHERE EXISTS (
>      SELECT 1
>      FROM b
>      WHERE b.i = a.i
> );
>
> Result:
> 2026-05-26 21:36:46.452 +07 [83331] LOG:  database system is ready to accept
> connections
> TRAP: failed Assert("target->ttype == PGPA_TARGET_IDENTIFIER"), File:
> "pgpa_trove.c", Line: 182, PID: 83390
> postgres: nkpit postgres [local] EXPLAIN(ExceptionalCondition+0x57)
> [0xa2d077]
> /tmp/pg/lib/postgresql/pg_plan_advice.so(+0xb0b2) [0x7fb2c06440b2]
> /tmp/pg/lib/postgresql/pg_plan_advice.so(+0x533f) [0x7fb2c063e33f]
> postgres: nkpit postgres [local] EXPLAIN(standard_planner+0x1ff) [0x7b51ff]
> ...
> Backtrace:
> #0  __pthread_kill_implementation (threadid=,
> signo=signo@entry=6,
>      no_tid=no_tid@entry=0) at pthread_kill.c:44
> #1  0x00007fb2bf27a8d3 in __pthread_kill_internal (threadid=,
> signo=6)
>      at pthread_kill.c:89
> #2  0x00007fb2bf21f48e in __GI_raise (sig=sig@entry=6) at
> ../sysdeps/posix/raise.c:26
> #3  0x00007fb2bf2067b3 in __GI_abort () at abort.c:77
> #4  0x0000000000a2d098 in ExceptionalCondition (
>      conditionName=conditionName@entry=0x7fb2c0649828 "target->ttype ==
> PGPA_TARGET_IDENTIFIER",
>      fileName=fileName@entry=0x7fb2c064a184 "pgpa_trove.c",
> lineNumber=lineNumber@entry=182)
>      at assert.c:65
> #5  0x00007fb2c06440b2 in pgpa_build_trove (advice_items=0x32a59488) at
> pgpa_trove.c:182
> #6  0x00007fb2c063e33f in pgpa_planner_setup (glob=0x32985888,
> parse=0x32956d60,
>      query_string=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT
> 1 FROM b WHERE b.i = a.i);", cursorOptions=2048,
> tuple_fraction=0x7ffe98a152f0, es=0x32985308) at pgpa_planner.c:255
> #7  0x00000000007b51ff in standard_planner (parse=0x32956d60,
> query_string=,
>      cursorOptions=2048, boundParams=, es=0x32985308) at
> planner.c:533
> #8  0x00000000007b5a0d in planner (parse=parse@entry=0x32956d60,
>      query_string=query_string@entry=0x329558b0 "EXPLAIN SELECT *\nFROM
> a\nWHERE EXISTS (SELECT 1 FROM b WHERE b.i = a.i);",
> cursorOptions=cursorOptions@entry=2048,
>      boundParams=boundParams@entry=0x0, es=es@entry=0x32985308) at
> planner.c:342
> #9  0x00000000008c297d in pg_plan_query
> (querytree=querytree@entry=0x32956d60,
>      query_string=query_string@entry=0x329558b0 "EXPLAIN SELECT *\nFROM
> a\nWHERE EXISTS (SELECT 1 FROM b WHERE b.i = a.i);", cursorOptions=2048,
> boundParams=boundParams@entry=0x0,
>      es=es@entry=0x32985308) at postgres.c:917
> #10 0x000000000062ed1e in standard_ExplainOneQuery (query=0x32956d60,
>      cursorOptions=, into=0x0, es=0x32985308,
>      queryString=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT 1
> FROM b WHERE b.i = a.i);", params=0x0, queryEnv=0x0) at explain.c:359
> #11 0x000000000062ef8e in ExplainOneQuery (query=,
> cursorOptions=,
>      into=, es=, pstate=,
> params=)
>      at explain.c:315
> --Type  for more, q to quit, c to continue without paging--c
> #12 0x000000000062f0ae in ExplainQuery (pstate=0x32982990, stmt=0x32956ba0,
> params=0x0,
>      dest=0x32985278) at ../../../src/include/nodes/nodes.h:178
> #13 0x00000000008c8819 in standard_ProcessUtility (pstmt=0x32956c50,
>      queryString=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT 1
> FROM b WHERE b.i = a.i);", readOnlyTree=,
> context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
>      dest=0x32985278, qc=0x7ffe98a15660) at utility.c:871
> #14 0x00000000008c6b8d in PortalRunUtility (portal=portal@entry=0x329f91e0,
> pstmt=0x32956c50,
>      isTopLevel=isTopLevel@entry=true,
> setHoldSnapshot=setHoldSnapshot@entry=true,
>      dest=dest@entry=0x32985278, qc=qc@entry=0x7ffe98a15660) at pquery.c:1149
> #15 0x00000000008c7060 in FillPortalStore (portal=portal@entry=0x329f91e0,
>      isTopLevel=isTopLevel@entry=true) at
> ../../../src/include/nodes/nodes.h:178
> #16 0x00000000008c737d in PortalRun (portal=portal@entry=0x329f91e0,
>      count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
>      dest=dest@entry=0x32a690d8, altdest=altdest@entry=0x32a690d8,
> qc=qc@entry=0x7ffe98a15830)
>      at pquery.c:756
> #17 0x00000000008c2f48 in exec_simple_query (
>      query_string=0x329558b0 "EXPLAIN SELECT *\nFROM a\nWHERE EXISTS (SELECT
> 1 FROM b WHERE b.i = a.i);") at postgres.c:1290
> #18 0x00000000008c4a21 in PostgresMain (dbname=,
> username=)
>      at postgres.c:4856
> #19 0x00000000008bea1d in BackendMain (startup_data=,
>      startup_data_len=) at backend_startup.c:124
> #20 0x00000000007fea2e in postmaster_child_launch (child_type= out>, child_slot=1,
>      startup_data=startup_data@entry=0x7ffe98a15c80,
> startup_data_len=startup_data_len@entry=24,
>      client_sock=client_sock@entry=0x7ffe98a15ca0) at launch_backend.c:268
> #21 0x0000000000802436 in BackendStartup (client_sock=0x7ffe98a15ca0) at
> postmaster.c:3627
> #22 ServerLoop () at postmaster.c:1728
> #23 0x0000000000803ef9 in PostmasterMain (argc=argc@entry=3,
> argv=argv@entry=0x328ff080)
>      at postmaster.c:1415
> #24 0x00000000004a1a68 in main (argc=3, argv=0x328ff080) at main.c:231
>
>
>
>

Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice

Hi, all

Tender Wang  于2026年5月27日周三 09:17写道：
>
> Tom Lane  于2026年5月27日周三 09:08写道：
> >
> > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?=  writes:
> > > Could this be related to GCC? I'm using GCC 16.1.1 and I can reproduce the crash with that version.
> >
> > Maybe.  Does it still fail if you set the optimization level to -O0 ?
> I can reproduce this crash on my machine with CFLAGS="-O0 -g3". And my
> GCC version is 11.4.0
diff --git a/contrib/pg_plan_advice/pgpa_trove.c
b/contrib/pg_plan_advice/pgpa_trove.c
index ca69f3bd3df..0d15af1cbba 100644
--- a/contrib/pg_plan_advice/pgpa_trove.c
+++ b/contrib/pg_plan_advice/pgpa_trove.c
@@ -179,9 +179,18 @@ pgpa_build_trove(List *advice_items)
                                         * but in the future this
might not be true, e.g. a custom
                                         * scan could replace a join.
                                         */
-                                       Assert(target->ttype ==
PGPA_TARGET_IDENTIFIER);
-                                       pgpa_trove_add_to_slice(&trove->scan,
-
                 item->tag, target);
+                                       if (target->ttype ==
PGPA_TARGET_IDENTIFIER)
+
pgpa_trove_add_to_slice(&trove->scan,
+
                         item->tag, target);
+                                       else
+                                       {
+                                               Assert(target->ttype
== PGPA_TARGET_ORDERED_LIST);
+
foreach_ptr(pgpa_advice_target, child_target, target->children)
+                                               {
+
pgpa_trove_add_to_slice(&trove->scan,
+
                         item->tag, child_target);
+                                               }
+                                       }
                                }

I tried the above fix, and no crash again.



-- 
Thanks,
Tender Wang

Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice

Hi all,

Ayush Tiwari  于2026年5月27日周三 15:16写道：
>
> Hi,
>
> On Wed, 27 May 2026 at 09:20, Tender Wang  wrote:
>>
>> Hi, all
>>
>> I find an easier way as follows:
>> diff --git a/contrib/pg_plan_advice/pgpa_trove.c
>> b/contrib/pg_plan_advice/pgpa_trove.c
>> index ca69f3bd3df..64af4b1435b 100644
>> --- a/contrib/pg_plan_advice/pgpa_trove.c
>> +++ b/contrib/pg_plan_advice/pgpa_trove.c
>> @@ -179,7 +179,6 @@ pgpa_build_trove(List *advice_items)
>>                                          * but in the future this
>> might not be true, e.g. a custom
>>                                          * scan could replace a join.
>>                                          */
>> -                                       Assert(target->ttype ==
>> PGPA_TARGET_IDENTIFIER);
>>                                         pgpa_trove_add_to_slice(&trove->scan,
>>
>>                  item->tag, target);
>>                                 }
>
>
> Thanks for checking this.
>
> I agree that removing the assertion looks like the better approach.

I attached a patch to fix this issue.

In syntax.sql, I saw this:
"
-- Tags like SEQ_SCAN and NO_GATHER don't allow sublists at all; other tags,
-- except for JOIN_ORDER, allow at most one level of sublist. Hence, these
-- examples should error out.
"
So 'DO_NOT_SCAN((x))' is valid syntax. The original codes in
pgpa_build_trove() may
forget about this case. I added this syntax case to the syntax.sql.

I also added the query to scan.sql and adjusted the original comments.

-- 
Thanks,
Tender Wang

Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice