Re: 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