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 <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: /messages/by-id/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 :

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

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
 

=?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

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

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

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

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

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

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