Re: Add custom EXPLAIN options support to auto_explain

On Mon, Mar 30, 2026 at 5:49 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I'm currently poking at some ideas for fixing this... more soon.

Here are some patches. I got started poking at this in earnest
because, on the pg_plan_advice thread, Lukas was saying that instead
of adopting pg_collect_advice, we should just add an option to send
advice strings for each executed query to the server log. I went to
implement that and then felt like it should really be part of
auto_explain rather than its own thing, which took me down a bit of a
rathole. But I eventually found my way back out of it, so here's a
patch set implementing auto_explain.log_extension_options.

There were two main complications. One is that I didn't want to assume
all options are Boolean. Right now, at least in core, they are: both
pg_overexplain and pg_plan_advice only use Booleans. But, that might
change in the future, or already be true in other extensions that
other people have written. Also, we might at some point in the future
want to consider folding the built-in options back into the same
mechanism -- i.e. get rid of log_extension_options and a pile of the
other parameters and just have log_options -- and then we'd need to
handle FORMAT, at least. The other big complication is that right now
there is no way to sanity check an EXPLAIN extension option except try
to set it and see if things go boom. That would mean that if you were
to configure auto_explain.log_extension_options='KANGAROO', the server
would basically just go down. I mean, technically it would be up, but
every query would ERROR when auto_explain tried to kangaroo-ify the
EXPLAIN output. That seems not so nice.

So I ended up with this:

- 0001 does a bit of refactoring to expose some of the logic in
SplitIdentifierString and similar functions in a way that allows for
reuse.
- 0002 modifies the "explain extension" option mechanism so that each
explain extension option has an associated GUC check handler.
- 0003 then uses those things to implement the feature

One thing that I suspect Tom in particular may be unhappy about (and
thus I'm copying him...) is that the validation framework in 0002
means that you have to load EXPLAIN-option providers before you can
use their options in the value of the new GUC. We try pretty hard to
make sure that the legal values for a GUC don't depend on the values
of other GUCs, and this is technically not a violation of that
principle because it's making the legal values for a GUC depend on
module loading, not other GUCs, so maybe it's fine. But on the other
hand, maybe it isn't. I'm not quite sure what the reasonable
alternative is: I guess we could instead add a "soft apply" hook for
EXPLAIN options where the contract is that the EXPLAIN option provider
is to do nothing if the option is not valid rather than complaining,
but then setting an invalid option wouldn't ever give any kind of
error -- it would just not work. That seems pretty unpleasant.

Anyway, if you apply all these patches it does solve the problem that
pg_collect_advice was targeting, modulo the need for some log parsing.
You can do this:

pg_plan_advice.always_store_advice_details = on
auto_explain.log_min_duration = 0
auto_explain.log_extension_options = 'plan_advice'

And then you get log output like this:

2026-03-31 12:16:18.784 EDT [75224] LOG: duration: 0.013 ms plan:
Query Text: select 1;
Result (cost=0.00..0.01 rows=1 width=4)
Generated Plan Advice:
NO_GATHER("*RESULT*")

Thoughts?

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