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