On Wed, May 27, 2020 at 10:48 AM David G. Johnston <david.g.johns...@gmail.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
