Thread
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
pg_plan_advice: DO_NOT_SCAN is a simple tag, not a generic one.
- b1901e2895e0 19 (unreleased) landed
-
doc: add comma to UPDATE docs, for consistency
- 9a41b34a2870 19 (unreleased) cited
-
Add stack depth check to QueueFKConstraintValidation().
- 61ea5cc6a61f 19 (unreleased) cited
-
BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
PG Bug reporting form <noreply@postgresql.org> — 2026-05-26T14:44:38Z
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=<optimized out>, signo=signo@entry=6, no_tid=no_tid@entry=0) at pthread_kill.c:44 #1 0x00007fb2bf27a8d3 in __pthread_kill_internal (threadid=<optimized out>, 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=<optimized out>, cursorOptions=2048, boundParams=<optimized out>, 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=<optimized out>, 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=<optimized out>, cursorOptions=<optimized out>, into=<optimized out>, es=<optimized out>, pstate=<optimized out>, params=<optimized out>) at explain.c:315 --Type <RET> 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=<optimized out>, 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=<optimized out>, username=<optimized out>) at postgres.c:4856 #19 0x00000000008bea1d in BackendMain (startup_data=<optimized out>, startup_data_len=<optimized out>) at backend_startup.c:124 #20 0x00000000007fea2e in postmaster_child_launch (child_type=<optimized 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
pierre.forstmann@gmail.com — 2026-05-26T17:52:03Z
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 <tgl@sss.pgh.pa.us> 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 <exclusion@gmail.com> Author: Ayush Tiwari <ayushtiwari.slg01@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> 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=<optimized out>, > signo=signo@entry=6, > no_tid=no_tid@entry=0) at pthread_kill.c:44 > #1 0x00007fb2bf27a8d3 in __pthread_kill_internal (threadid=<optimized out>, > 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=<optimized out>, > cursorOptions=2048, boundParams=<optimized out>, 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=<optimized out>, 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=<optimized out>, > cursorOptions=<optimized out>, > into=<optimized out>, es=<optimized out>, pstate=<optimized out>, > params=<optimized out>) > at explain.c:315 > --Type <RET> 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=<optimized out>, > 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=<optimized out>, > username=<optimized out>) > at postgres.c:4856 > #19 0x00000000008bea1d in BackendMain (startup_data=<optimized out>, > startup_data_len=<optimized out>) at backend_startup.c:124 > #20 0x00000000007fea2e in postmaster_child_launch (child_type=<optimized > 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
Michael Paquier <michael@paquier.xyz> — 2026-05-27T00:49:55Z
On Tue, May 26, 2026 at 07:52:03PM +0200, Pierre Forstmann wrote: > I am not sure pg_plan_advice is supported with PG 18.4 ? Nope, the module is not supported on 18.4. > I cannot reproduce with latest commit from PG 19 master branch: But if you can find a problem while testing the module only on HEAD, that would be a bug we would need to take care of. -- Michael
-
Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
Никита Калинин <n.kalinin@postgrespro.ru> — 2026-05-27T00:59:14Z
> I am not sure pg_plan_advice is supported with PG 18.4 ? Sorry, the form on the website doesn't allow selecting the master branch, so I have to choose the latest available version instead. But yes, you're right — pg_plan_advice is only available on the master branch. > I cannot reproduce with latest commit from PG 19 master branch Could this be related to GCC? I'm using GCC 16.1.1 and I can reproduce the crash with that version. version ------------------------------------------------------------------------------------------------------------- PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 16.1.1 20260515 (Red Hat 16.1.1-2), 64-bit (1 row) commit 9a41b34a28702a71cc0a0e77dd80ca80366d12fb (HEAD -> master, origin/master, origin/HEAD) Author: Bruce Momjian <bruce@momjian.us> Date: Tue May 26 20:17:40 2026 -0400 2026-05-27 07:52:49.541 +07 [353027] LOG: database system was shut down at 2026-05-27 07:52:49 +07 2026-05-27 07:52:49.543 +07 [353022] LOG: database system is ready to accept connections TRAP: failed Assert("target->ttype == PGPA_TARGET_IDENTIFIER"), File: "pgpa_trove.c", Line: 182, PID: 353087 postgres: nkpit postgres ::1(45722) EXPLAIN(ExceptionalCondition+0x57) [0xa2d0b7] /tmp/pg/lib/postgresql/pg_plan_advice.so(+0xb0b2) [0x7f2bd83c70b2] /tmp/pg/lib/postgresql/pg_plan_advice.so(+0x533f) [0x7f2bd83c133f] -- Nikita Kalinin -
Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-27T01:08:19Z
=?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <n.kalinin@postgrespro.ru> 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 ? regards, tom lane
-
Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
Tender Wang <tndrwang@gmail.com> — 2026-05-27T01:17:39Z
Tom Lane <tgl@sss.pgh.pa.us> 于2026年5月27日周三 09:08写道： > > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <n.kalinin@postgrespro.ru> 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 -- Thanks, Tender Wang
-
Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
Tender Wang <tndrwang@gmail.com> — 2026-05-27T01:28:42Z
Hi, all Tender Wang <tndrwang@gmail.com> 于2026年5月27日周三 09:17写道： > > Tom Lane <tgl@sss.pgh.pa.us> 于2026年5月27日周三 09:08写道： > > > > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <n.kalinin@postgrespro.ru> 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
Tender Wang <tndrwang@gmail.com> — 2026-05-27T03:50:09Z
Hi, all Tender Wang <tndrwang@gmail.com> 于2026年5月27日周三 09:28写道： > > Hi, all > > Tender Wang <tndrwang@gmail.com> 于2026年5月27日周三 09:17写道： > > > > Tom Lane <tgl@sss.pgh.pa.us> 于2026年5月27日周三 09:08写道： > > > > > > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <n.kalinin@postgrespro.ru> 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. 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); } Just remove the Assert, then it works as well. The previous fix is not ok, because the output of explain is not the same as the user input: Supplied Plan Advice: DO_NOT_SCAN(a) /* matched, failed */ We should get DO_NOT_SCAN((a)) The new fix will get what we want: postgres=# EXPLAIN SELECT * FROM a WHERE EXISTS ( SELECT 1 FROM b WHERE b.i = a.i ); 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) In pgpa_identifier_matches_target(), if it is not the PGPA_TARGET_IDENTIFIER, we will check all descendants. The original comments may need to be adjusted. I added Robert to the cc list. He knows more about pg_plan_advice than I. -- Thanks, Tender Wang -
Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-05-27T07:16:24Z
Hi, On Wed, 27 May 2026 at 09:20, Tender Wang <tndrwang@gmail.com> 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. Keeping the original target tree seems preferable. As you noted, pgpa_identifier_matches_target() already handles non-identifier targets by checking their descendants. pgpa_trove_add_to_hash() does the same when building the lookup table, so a grouped target such as ((a)) should still be indexed and matched through its child identifier while preserving the original shape for output. So I think the assertion in pgpa_build_trove() is too strict, and the nearby comment should be adjusted to avoid saying/implying that scan advice always has a direct identifier target. Regards, Ayush
-
Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
Tender Wang <tndrwang@gmail.com> — 2026-05-29T02:03:50Z
Hi all, Ayush Tiwari <ayushtiwari.slg01@gmail.com> 于2026年5月27日周三 15:16写道： > > Hi, > > On Wed, 27 May 2026 at 09:20, Tender Wang <tndrwang@gmail.com> 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
Robert Haas <robertmhaas@gmail.com> — 2026-05-29T18:02:25Z
On Thu, May 28, 2026 at 10:04 PM Tender Wang <tndrwang@gmail.com> wrote: > -- 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 for the analysis and the patch, but in fact DO_NOT_SCAN() was intended to be a "simple" tag, not a "generic" one, and I just messed up. This makes sense if you think through how it actually works. For a tag like GATHER, GATHER((x y)) means something different form GATHER(x y): the former means that there should be a Gather node on top of the join between x and y, while the latter means that there should be two separate Gather nodes, one atop x and the other atop y. On the other hand, NO_GATHER(x y) means that no Gather node can appear anywhere above x or y, and there is no such thing as NO_GATHER((x y)) because it couldn't mean anything different. Likewise, SEQ_SCAN(x) means use a sequential scan on x, and SEQ_SCAN((x)) or SEQ_SCAN((x y)) is refused because you can't use a sequential scan on a group of tables. Extending that reasoning to the current case, DO_NOT_SCAN() is like SEQ_SCAN() or NO_GATHER(): it applies to a single relation, not to a list of relations. However, for things to actually work that way, pgpa_scanner.l needs to classify it as TOK_TAG_SIMPLE, and a corresponding adjustment is needed in pgpa_parser.y. I overlooked the need for this in the patch that introduced DO_NOT_SCAN. I have committed a fix. -- Robert Haas EDB: http://www.enterprisedb.com