MIME-Version: 1.0
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>
 <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
In-Reply-To: <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Mon, 17 Nov 2025 10:09:36 -0500
Message-ID: 
 <CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Matheus Alcantara <matheusssilv97@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoaf__2B0BUL%2Bvrg28P%2B3buX%3DTi-kybqkHiLTtFrrCfzuA%40mail.gmail.com>
Precedence: bulk

On Mon, Nov 17, 2025 at 9:42=E2=80=AFAM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:
> 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?

Cool, thanks for looking. I am guessing that the paucity of feedback
thus far is partly because there's a lot of stuff to absorb -- though
the main point at this stage is really to get some opinions on the
planner infrastructure/hooks, which don't necessarily require full
understanding of (never mind agreement with) the design of
pg_plan_advice itself.

> 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.

This is just an idiosyncrasy of PostgreSQL's extension framework.
Whether or not EXPLAIN (PLAN_ADVICE) works depends on whether the
shared module has been loaded, not whether the extension has been
created. The purpose of CREATE EXTENSION is to put SQL objects, such
as function definitions, into the database, but there's no SQL
required to enable EXPLAIN (PLAN_ADVICE) -- or for setting the
pg_plan_advice.advice GUC. However, running CREATE EXTENSION to
establish the function definitions will incidentally load the shared
module into that particular session.

Therefore, the best way to use this module is to add pg_plan_advice to
shared_preload_libraries. Alternatively, you can use
session_preload_libraries or run LOAD in an individual session. If you
don't care about the collector interface, that's really all you need.
If you do care about the collector interface, then in addition you
will need to run CREATE EXTENSION, so that the SQL functions needed to
access it are available.

--=20
Robert Haas
EDB: http://www.enterprisedb.com