Thread: Explain Analyze (Rollback off) Suggestion

On Wed, May 27, 2020 at 10:48 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> The recent discussion about EXPLAIN and the possible inclusion of default-specifying GUCs raised a behavior that I
didnot fully appreciate nor find to be self-evident.  Running EXPLAIN ANALYZE results in any side-effects of the
explainedand analyzed statement being permanently written to the current transaction - which is in many cases is
implicitlyimmediately committed unless the user takes care otherwise.  This seems like an implementation expedient
behaviorbut an unfriendly default.  It doesn't seem unreasonable for a part-time dba to expect an explain outcome to
alwaysbe non-persistent, even in ANALYZE mode since the execution of that command could be done in a transaction (or
savepoint...)and then immediately undone before sending the explain output to the client. 
>
> I'm against having a GUC that implicitly triggers an ANALYZE version of the EXPLAIN command.  I also think that it
wouldbe worth the effort to try and make EXPLAIN ANALYZE default to using auto-rollback behavior.  Overriding that
defaultbehavior could be done on a per command basis by specifying the option "ROLLBACK off".  With the new GUCs users
thatfind themselves in the situation of needing a non-permanent outcome across multiple commands could then get back to
theless safe behavior by setting the corresponding GUC to off in their session.  I won't pretend to have any idea how
oftenthat would be useful - especially as it would depend upon whether the auto-savepoint idea is workable or whether
theclient has to be outside of a transaction in order for the rollback limited behavior to work. 

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.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.

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.

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.

            regards, tom lane

On Wed, May 27, 2020 at 9:33 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I'm not seeing enough similarity with the reasons for, and specific behaviors, of those previous GUCs to dismiss this
proposalon that basis alone.  These are "crap we messed things up" switches that alter a query behind the scenes in
waysthat a user cannot do through SQL - they simply provide for changing a default that we already allow the user to
overrideper-query.  Its akin to "DateStyle" and its pure cosmetic influencing ease-of-use option rather than some
changingthe fundamental structural meaning of '\n' 

Well, I think it's usually worse to have two possible behaviors rather
than one. Like, a lot of people have probably made the mistake of
running EXPLAIN ANALYZE without realizing that it's actually running
the query, and then been surprised or dismayed afterwards. But each
person only has to learn that once. If we had a GUC controlling this
behavior, then you'd have to always be aware of the setting on any
particular system on which you might be thinking of running the
command. Likewise, if you write an application or tool of some sort
that uses EXPLAIN ANALYZE, it has to be aware of the GUC value, or it
won't work as expected on some systems.

This is the general problem with behavior-changing GUCs. I kind of
have mixed feelings about this. On the one hand, it sucks for
operators of individual systems not to be able to customize things so
as to produce the behavior they want. On the other hand, each one you
add makes it harder to write code that will work the same way on every
PostgreSQL system. I don't think the problem would be as bad in this
particular case as in some others that have been proposed, mostly
because EXPLAIN ANALYZE isn't widely-used by applications, so maybe
it's worth considering. But on the whole, I'm inclined to agree with
Tom that it's better not to create too many ways for fundamental
behavior of the system to vary from one installation to another.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The ANALYZE option should not be part of the GUC setup.

Yeah.  While I'm generally not in favor of putting GUCs into the mix
here, the only one that seriously scares me is a GUC that would affect
whether the EXPLAIN'd query executes or not.  A GUC that causes buffer
counts to be reported/not-reported is not going to result in data
destruction when someone forgets that it's on.

(BTW, adding an option for auto-rollback wouldn't change my opinion
about that.  Not all side-effects of a query can be rolled back.  Thus,
if there is an auto-rollback option, it mustn't be GUC-adjustable
either.)

            regards, tom lane