Re: pg_plan_advice

Hi

On Thu Nov 6, 2025 at 1:45 PM -03, Robert Haas wrote:
> Here's v3. I've attempted to fix some more things that cfbot didn't
> like, one of which was an actual bug in 0005, and I also fixed a
> stupid few bugs in pgpa_collector.c and added a few more tests.
>
I've spent some time playing with these patches. I still don't have to
much comments on the syntax yet but I've noticed a small bug or perhaps
I'm missing something?

When I run CREATE EXTENSION pg_plan_advice I'm able to use the
EXPLAIN(plan_advice) but if try to open another connection, with the
extension already previously created, I'm unable to use once I drop and
re-create the extension.

tpch=# create extension pg_plan_advice;
ERROR:  extension "pg_plan_advice" already exists
tpch=# explain(plan_advice) select 1;
ERROR:  unrecognized EXPLAIN option "plan_advice"
LINE 1: explain(plan_advice) select 1;
                ^
tpch=# drop extension pg_plan_advice ;
DROP EXTENSION
tpch=# create extension pg_plan_advice;
CREATE EXTENSION
tpch=# explain(plan_advice) select 1;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4)
 Generated Plan Advice:
   NO_GATHER("*RESULT*")


And thanks for working on this. I think that this can be a very useful
feature for both users and for postgres hackers, +1 for the idea.

--
Matheus Alcantara
EDB: http://www.enterprisedb.com