public inbox for [email protected]
help / color / mirror / Atom feedBUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
11+ messages / 8 participants
[nested] [flat]
* BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
@ 2026-05-26 14:44 PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: PG Bug reporting form @ 2026-05-26 14:44 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19493
Logged by: Nikita Kalinin
Email address: [email protected]
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
@ 2026-05-26 17:52 ` Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Pierre Forstmann @ 2026-05-26 17:52 UTC (permalink / raw)
To: [email protected]; [email protected]; PG Bug reporting form <[email protected]>
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 <[email protected]>
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 <[email protected]>
Author: Ayush Tiwari <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
Discussion: https://postgr.es/m/[email protected]
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: [email protected]
> 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
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
@ 2026-05-27 00:49 ` Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Paquier @ 2026-05-27 00:49 UTC (permalink / raw)
To: Pierre Forstmann <[email protected]>; +Cc: [email protected]; [email protected]; PG Bug reporting form <[email protected]>
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
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
@ 2026-05-27 00:59 ` Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Калинин Никита @ 2026-05-27 00:59 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: [email protected]; PG Bug reporting form <[email protected]>; Pierre Forstmann <[email protected]>
> 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 <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
@ 2026-05-27 01:08 ` Tom Lane <[email protected]>
2026-05-27 01:17 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2026-05-27 01:08 UTC (permalink / raw)
To: Калинин Никита <[email protected]>; +Cc: Michael Paquier <[email protected]>; [email protected]; Pierre Forstmann <[email protected]>
=?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
@ 2026-05-27 01:17 ` Tender Wang <[email protected]>
2026-05-27 01:28 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tender Wang @ 2026-05-27 01:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Калинин Никита <[email protected]>; Michael Paquier <[email protected]>; [email protected]; Pierre Forstmann <[email protected]>
Tom Lane <[email protected]> 于2026年5月27日周三 09:08写道：
>
> =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
2026-05-27 01:17 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
@ 2026-05-27 01:28 ` Tender Wang <[email protected]>
2026-05-27 03:50 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tender Wang @ 2026-05-27 01:28 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Калинин Никита <[email protected]>; Michael Paquier <[email protected]>; [email protected]; Pierre Forstmann <[email protected]>
Hi, all
Tender Wang <[email protected]> 于2026年5月27日周三 09:17写道：
>
> Tom Lane <[email protected]> 于2026年5月27日周三 09:08写道：
> >
> > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
2026-05-27 01:17 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 01:28 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
@ 2026-05-27 03:50 ` Tender Wang <[email protected]>
2026-05-27 07:16 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tender Wang @ 2026-05-27 03:50 UTC (permalink / raw)
To: [email protected]; +Cc: Калинин Никита <[email protected]>; Michael Paquier <[email protected]>; Pierre Forstmann <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>
Hi, all
Tender Wang <[email protected]> 于2026年5月27日周三 09:28写道：
>
> Hi, all
>
> Tender Wang <[email protected]> 于2026年5月27日周三 09:17写道：
> >
> > Tom Lane <[email protected]> 于2026年5月27日周三 09:08写道：
> > >
> > > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
2026-05-27 01:17 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 01:28 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 03:50 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
@ 2026-05-27 07:16 ` Ayush Tiwari <[email protected]>
2026-05-29 02:03 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Ayush Tiwari @ 2026-05-27 07:16 UTC (permalink / raw)
To: Tender Wang <[email protected]>; +Cc: [email protected]; Калинин Никита <[email protected]>; Michael Paquier <[email protected]>; Pierre Forstmann <[email protected]>; Robert Haas <[email protected]>; Tom Lane <[email protected]>
Hi,
On Wed, 27 May 2026 at 09:20, Tender Wang <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
2026-05-27 01:17 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 01:28 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 03:50 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 07:16 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Ayush Tiwari <[email protected]>
@ 2026-05-29 02:03 ` Tender Wang <[email protected]>
2026-05-29 18:02 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Robert Haas <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tender Wang @ 2026-05-29 02:03 UTC (permalink / raw)
To: [email protected]; +Cc: Robert Haas <[email protected]>; Tom Lane <[email protected]>; Ayush Tiwari <[email protected]>; Калинин Никита <[email protected]>; Michael Paquier <[email protected]>; Pierre Forstmann <[email protected]>
Hi all,
Ayush Tiwari <[email protected]> 于2026年5月27日周三 15:16写道：
>
> Hi,
>
> On Wed, 27 May 2026 at 09:20, Tender Wang <[email protected]> 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
Attachments:
[application/octet-stream] 0001-pg_plan_advice-fix-assertion-failure-with-ordered-li.patch (5.3K, 2-0001-pg_plan_advice-fix-assertion-failure-with-ordered-li.patch)
download | inline diff:
From 122bf5295b4cc46200655d11ec17e4d8f6d72902 Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Fri, 29 May 2026 09:37:46 +0800
Subject: [PATCH] pg_plan_advice: fix assertion failure with ordered-list scan
targets
Scan advice targets are not always represented as
PGPA_TARGET_IDENTIFIER. Some valid advice entries may use
PGPA_TARGET_ORDERED_LIST targets, causing the assertion in
pgpa_build_trove() to fail.
Remove the overly restrictive assertion and allow all valid
scan advice target types to be added to the scan trove.
Add a regression test covering ordered-list scan targets.
---
contrib/pg_plan_advice/expected/scan.out | 22 ++++++++++++++++++++++
contrib/pg_plan_advice/expected/syntax.out | 6 ++++++
contrib/pg_plan_advice/pgpa_trove.c | 9 +++++----
contrib/pg_plan_advice/sql/scan.sql | 7 +++++++
contrib/pg_plan_advice/sql/syntax.sql | 4 ++++
5 files changed, 44 insertions(+), 4 deletions(-)
diff --git a/contrib/pg_plan_advice/expected/scan.out b/contrib/pg_plan_advice/expected/scan.out
index f4036e4cbdd..013ed1aa54e 100644
--- a/contrib/pg_plan_advice/expected/scan.out
+++ b/contrib/pg_plan_advice/expected/scan.out
@@ -769,6 +769,28 @@ SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0);
NO_GATHER(unnamed_subquery s@unnamed_subquery)
(7 rows)
+COMMIT;
+-- Test sublist in Tags
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN((s1))';
+EXPLAIN(COSTS OFF, PLAN_ADVICE)
+SELECT * FROM scan_table s1 WHERE EXISTS (SELECT 1 FROM scan_table s2 WHERE s1.a = s2.a OFFSET 0);
+ QUERY PLAN
+----------------------------------------------------------------
+ Seq Scan on scan_table s1
+ Disabled: true
+ Filter: EXISTS(SubPlan exists_1)
+ SubPlan exists_1
+ -> Index Only Scan using scan_table_pkey on scan_table s2
+ Index Cond: (a = s1.a)
+ Supplied Plan Advice:
+ DO_NOT_SCAN((s1)) /* matched, failed */
+ Generated Plan Advice:
+ SEQ_SCAN(s1)
+ INDEX_ONLY_SCAN(s2@exists_1 public.scan_table_pkey)
+ NO_GATHER(s1 s2@exists_1)
+(12 rows)
+
COMMIT;
-- Test a non-repeatable tablesample method with a scan-level Materialize.
CREATE EXTENSION tsm_system_time;
diff --git a/contrib/pg_plan_advice/expected/syntax.out b/contrib/pg_plan_advice/expected/syntax.out
index c3f2cbd6dca..3366e544ce0 100644
--- a/contrib/pg_plan_advice/expected/syntax.out
+++ b/contrib/pg_plan_advice/expected/syntax.out
@@ -129,6 +129,12 @@ DETAIL: Could not parse advice: syntax error at or near "("
SET pg_plan_advice.advice = 'GATHER(((x)))';
ERROR: invalid value for parameter "pg_plan_advice.advice": "GATHER(((x)))"
DETAIL: Could not parse advice: syntax error at or near "("
+-- success
+SET pg_plan_advice.advice = 'DO_NOT_SCAN((x))';
+-- fail
+SET pg_plan_advice.advice = 'DO_NOT_SCAN(((x)))';
+ERROR: invalid value for parameter "pg_plan_advice.advice": "DO_NOT_SCAN(((x)))"
+DETAIL: Could not parse advice: syntax error at or near "("
-- Legal comments.
SET pg_plan_advice.advice = '/**/';
EXPLAIN (COSTS OFF) SELECT 1;
diff --git a/contrib/pg_plan_advice/pgpa_trove.c b/contrib/pg_plan_advice/pgpa_trove.c
index ca69f3bd3df..72c7fd025d3 100644
--- a/contrib/pg_plan_advice/pgpa_trove.c
+++ b/contrib/pg_plan_advice/pgpa_trove.c
@@ -175,11 +175,12 @@ pgpa_build_trove(List *advice_items)
foreach_ptr(pgpa_advice_target, target, item->targets)
{
/*
- * For now, all of our scan types target single relations,
- * but in the future this might not be true, e.g. a custom
- * scan could replace a join.
+ * Scan advice commonly targets a single relation, but this is not
+ * guaranteed by the representation: targets may also be ordered lists,
+ * and future scan advice might cover more complex targets such as a
+ * custom scan replacing a join. Therefore, do not assume identifier-only
+ * targets here.
*/
- Assert(target->ttype == PGPA_TARGET_IDENTIFIER);
pgpa_trove_add_to_slice(&trove->scan,
item->tag, target);
}
diff --git a/contrib/pg_plan_advice/sql/scan.sql b/contrib/pg_plan_advice/sql/scan.sql
index 98bee88de91..1440c93bc70 100644
--- a/contrib/pg_plan_advice/sql/scan.sql
+++ b/contrib/pg_plan_advice/sql/scan.sql
@@ -197,6 +197,13 @@ EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0);
COMMIT;
+-- Test sublist in Tags
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN((s1))';
+EXPLAIN(COSTS OFF, PLAN_ADVICE)
+SELECT * FROM scan_table s1 WHERE EXISTS (SELECT 1 FROM scan_table s2 WHERE s1.a = s2.a OFFSET 0);
+COMMIT;
+
-- Test a non-repeatable tablesample method with a scan-level Materialize.
CREATE EXTENSION tsm_system_time;
CREATE TABLE scan_tsm (i int);
diff --git a/contrib/pg_plan_advice/sql/syntax.sql b/contrib/pg_plan_advice/sql/syntax.sql
index f274fa48636..c9c5eb84f3a 100644
--- a/contrib/pg_plan_advice/sql/syntax.sql
+++ b/contrib/pg_plan_advice/sql/syntax.sql
@@ -42,6 +42,10 @@ SET pg_plan_advice.advice = '123';
-- examples should error out.
SET pg_plan_advice.advice = 'SEQ_SCAN((x))';
SET pg_plan_advice.advice = 'GATHER(((x)))';
+-- success
+SET pg_plan_advice.advice = 'DO_NOT_SCAN((x))';
+-- fail
+SET pg_plan_advice.advice = 'DO_NOT_SCAN(((x)))';
-- Legal comments.
SET pg_plan_advice.advice = '/**/';
--
2.34.1
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Michael Paquier <[email protected]>
2026-05-27 00:59 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Калинин Никита <[email protected]>
2026-05-27 01:08 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tom Lane <[email protected]>
2026-05-27 01:17 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 01:28 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 03:50 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
2026-05-27 07:16 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Ayush Tiwari <[email protected]>
2026-05-29 02:03 ` Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice Tender Wang <[email protected]>
@ 2026-05-29 18:02 ` Robert Haas <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Robert Haas @ 2026-05-29 18:02 UTC (permalink / raw)
To: Tender Wang <[email protected]>; +Cc: [email protected]; Tom Lane <[email protected]>; Ayush Tiwari <[email protected]>; Калинин Никита <[email protected]>; Michael Paquier <[email protected]>; Pierre Forstmann <[email protected]>
On Thu, May 28, 2026 at 10:04 PM Tender Wang <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-05-29 18:02 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-26 14:44 BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice PG Bug reporting form <[email protected]>
2026-05-26 17:52 ` Pierre Forstmann <[email protected]>
2026-05-27 00:49 ` Michael Paquier <[email protected]>
2026-05-27 00:59 ` Калинин Никита <[email protected]>
2026-05-27 01:08 ` Tom Lane <[email protected]>
2026-05-27 01:17 ` Tender Wang <[email protected]>
2026-05-27 01:28 ` Tender Wang <[email protected]>
2026-05-27 03:50 ` Tender Wang <[email protected]>
2026-05-27 07:16 ` Ayush Tiwari <[email protected]>
2026-05-29 02:03 ` Tender Wang <[email protected]>
2026-05-29 18:02 ` Robert Haas <[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