Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Mon, 17 Nov 2025 11:42:45 -0300
Message-Id: <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
Cc: "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_plan_advice
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Robert Haas" <robertmhaas@gmail.com>, "Jakub Wartak"
 <jakub.wartak@enterprisedb.com>
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
In-Reply-To: 
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DEB1SAGVDX7S.JMV96X4R0RCS%40gmail.com>
Precedence: bulk

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=3D# create extension pg_plan_advice;
ERROR:  extension "pg_plan_advice" already exists
tpch=3D# explain(plan_advice) select 1;
ERROR:  unrecognized EXPLAIN option "plan_advice"
LINE 1: explain(plan_advice) select 1;
                ^
tpch=3D# drop extension pg_plan_advice ;
DROP EXTENSION
tpch=3D# create extension pg_plan_advice;
CREATE EXTENSION
tpch=3D# explain(plan_advice) select 1;
                QUERY PLAN
------------------------------------------
 Result  (cost=3D0.00..0.01 rows=3D1 width=3D4)
 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.

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