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

Hi, all

Tender Wang <tndrwang(at)gmail(dot)com> 于2026年5月27日周三 09:28写道：
>
> Hi, all
>
> Tender Wang <tndrwang(at)gmail(dot)com> 于2026年5月27日周三 09:17写道：
> >
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 于2026年5月27日周三 09:08写道：
> > >
> > > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <n(dot)kalinin(at)postgrespro(dot)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