Re: making EXPLAIN extensible - Mailing list pgsql-hackers

On Fri, Feb 28, 2025 at 3:09 PM Thom Brown <thom@linux.com> wrote:
> "pg_overexplain"? I love this name! And the idea sounds like a natural
> evolution, so +1.

Thanks. I thought about things like pg_hyperexplain or
pg_explain_debug, but in the end I didn't like any of them better than
overexplain. :-)

> One thing I am wondering is whether extensions should be required to
> prefix their EXPLAIN option with the extension name to avoid
> collisions.

I considered that. One advantage of doing that is that you could
support autoloading. Right now, you have to LOAD 'pg_overexplain' or
put it in session_preload_libraries or shared_preload_libraries in
order to use it. If you required people to type EXPLAIN
(pg_overexplain.range_table) instead of just EXPLAIN (range_table),
then you could react to not finding any such option by trying to
autoload a .so with the part of the name before the dot.

But you can probably see that this idea has a couple of pretty serious
weaknesses:

1. It is much more verbose. I theorize that people will be unhappy
about having to type EXPLAIN (pg_overexplain.range_table) rather than
just EXPLAIN (range_table). One could try to address this by renaming
the extension to something shorter, like just 'oe'. Having to type
EXPLAIN (oe.range_table) wouldn't be nearly as annoying. However, this
seems like a pretty clear case of letting the tail wag the dog.

2. autoloading could have security concerns. This is probably fixable,
but we'd need to be sure that providing a new way to trigger loading a
module didn't open up any security holes.

> If two extensions happen to choose the same name, it won't be possible
> to use both simultaneously.

That's true. Of course, a lot depends on whether we end up with 3 or 5
or 8 EXPLAIN extensions or more like 30 or 50 or 80. In the former
case, the people writing those extensions will probably mostly know
about each other and can just use different names. In the latter case
it's a problem. My guess is it's the former case.

> In what order would the options be applied? Would it be deterministic,
> or weighted within the extension's configuration, or based on the
> order of the options in the list?

I'm not entirely sure I know which question you're asking here. If
you're asking what happens if two modules try to register the same
EXPLAIN option name and then a user uses it, one of the registrations
will win and the other will lose. I think the second one wins. As I
say above, I assume we'll find a way to not try to do that. However, I
think more likely you're asking: if you load pg_fingernailexplain and
pg_toenailexplain and then do EXPLAIN (toenail, fingernail) SELECT
..., in what order will the options take effect? For the answer to
that question, see the commit message for 0002.

> Would explain extensions be capable of modifying pre-existing core
> option output, or just append to output?

The interfaces we have are really only going to work for appending.
Modifying would be cool, but I think it's mostly impractical. We have
a framework for emitting stuff into EXPLAIN output in a way that takes
into account whether you're in text mode or json or yaml or whatever,
and this patch just builds on that existing framework to allow you to
make extra calls to those emit-some-output functions at useful places.
As a result, the patch is small and simple. If we had an existing
framework for modifying stuff, then we could perhaps provide suitable
places to call those functions, too. But they don't exist, and it's
not easy to see how they could be created. I think you would need some
kind of major redesign of explain.c, and I don't know how to do that
without making it bloated, slow, and unmaintainable.

If somebody comes up with a way of allowing certain limited types of
modifications to EXPLAIN output with small, elegant-looking code
changes, and if those changes seem like useful things for an extension
to want to do, I'm totally on board. But I currently don't have an
idea like that.

> Should there be a way of determining which lines are output by which
> option? An extension may output similar output to core output, making
> it difficult or impossible to discern which is which.

I don't think this is really going to be a problem.

> Does there need to be any security considerations so that things like
> RLS don't inadvertently become leaky?

It's possible that there may be some security considerations, and
that's worth thinking about. However, RLS disclaims support for
side-channel attacks, so it's already understood to be (very) leaky.

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