Обсуждение: Explain Analyze (Rollback off) Suggestion
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 cannot make this happen even if there is interest but it seems like a good time to bring up the idea.
David J.
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 5:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
Agreed
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.
The "safety" aspect of this is a motivator but at least having the option exist makes users both more aware and also simplifies usage, so ok.
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.
I'm not seeing enough similarity with the reasons for, and specific behaviors, of those previous GUCs to dismiss this proposal on that basis alone. These are "crap we messed things up" switches that alter a query behind the scenes in ways that a user cannot do through SQL - they simply provide for changing a default that we already allow the user to override per-query. Its akin to "DateStyle" and its pure cosmetic influencing ease-of-use option rather than some changing the fundamental structural meaning of '\n'
If that isn't enough then I would just drop the idea since I don't see enough benefit to introducing a wrapper layer in psql on top of explain.
David J.
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
On Thu, May 28, 2020 at 6:42 AM Robert Haas <robertmhaas@gmail.com> wrote:
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 proposal on that basis alone. These are "crap we messed things up" switches that alter a query behind the scenes in ways that a user cannot do through SQL - they simply provide for changing a default that we already allow the user to override per-query. Its akin to "DateStyle" and its pure cosmetic influencing ease-of-use option rather than some changing the 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.
This really belongs on the other thread (though I basically said the same thing there two days ago):
The ANALYZE option should not be part of the GUC setup. None of the other EXPLAIN default changing options have the same issues with being on by default - which is basically what we are talking about here: being able to have an option be on without specifying that option in the command itself. TIMING already does this without difficulty and the others are no different.
David J.
"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
On Thu, May 28, 2020 at 7:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
(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.)
Yeah, I've worked myself around to that as well, this thread's proposal would be to just make setting up rollback more obvious and easier for a user of explain analyze - whose value at this point is wholly independent of the GUC discussion.
David J.