Re: Explain Analyze (Rollback off) Suggestion

On Wed, May 27, 2020 at 5:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I think the only way to make the effects of an EXPLAIN ANALYZE
> > statement be automatically rolled back would be to wrap the entire
> > operation in a subtransaction. While we could certainly implement
> > that, it might have its own share of surprises; for example, it would
> > consume an XID, leading to faster wraparound vacuums if you do it
> > frequently.
>
> Right, but it's just automating something that people now do by hand
> (ie manually wrap the EXPLAIN in BEGIN/ROLLBACK) when that's what they
> need. I think the idea of having an option to do it for you isn't bad.
>

Agreed

> I'm strongly against changing the very-longstanding default behavior of
> EXPLAIN ANALYZE, though; the villagers at your doorstep will not be
> bringing flowers. So this new option has to *not* default to on.
>

The "safety" aspect of this is a motivator but at least having the option
exist makes users both more aware and also simplifies usage, so ok.

> As far as the general topic of the thread goes, I like the idea of
> controlling EXPLAIN options on the client side way better than inventing
> statement-behavior-altering GUCs. We learned our lesson about that a
> decade or two back; only those who don't remember propose new ones.
>

I'm not seeing enough similarity with the reasons for, and specific
behaviors, of those previous GUCs to dismiss this proposal on that basis
alone. These are "crap we messed things up" switches that alter a query
behind the scenes in ways that a user cannot do through SQL - they simply
provide for changing a default that we already allow the user to override
per-query. Its akin to "DateStyle" and its pure cosmetic influencing
ease-of-use option rather than some changing the fundamental structural
meaning of '\n'

If that isn't enough then I would just drop the idea since I don't see
enough benefit to introducing a wrapper layer in psql on top of explain.

David J.