Re: Explain Analyze (Rollback off) Suggestion

On Wed, May 27, 2020 at 10:48 AM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> The recent discussion about EXPLAIN and the possible inclusion of default-specifying GUCs raised a behavior that I did not fully appreciate nor find to be self-evident. Running EXPLAIN ANALYZE results in any side-effects of the explained and analyzed statement being permanently written to the current transaction - which is in many cases is implicitly immediately committed unless the user takes care otherwise. This seems like an implementation expedient behavior but an unfriendly default. It doesn't seem unreasonable for a part-time dba to expect an explain outcome to always be 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 would be worth the effort to try and make EXPLAIN ANALYZE default to using auto-rollback behavior. Overriding that default behavior could be done on a per command basis by specifying the option "ROLLBACK off". With the new GUCs users that find themselves in the situation of needing a non-permanent outcome across multiple commands could then get back to the less safe behavior by setting the corresponding GUC to off in their session. I won't pretend to have any idea how often that would be useful - especially as it would depend upon whether the auto-savepoint idea is workable or whether the client 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