Обсуждение: Volatility - docs vs behaviour?
Hi all The docs say: "For best optimization results, you should label your functions with the strictest volatility category that is valid for them." http://www.postgresql.org/docs/current/interactive/xfunc-volatility.html ... but I recall discussion here suggesting that in fact IMMUTABLE functions may not be inlined where you'd expect, e.g. http://www.postgresql.org/message-id/CAFj8pRBF3Qr7WtQwO1H_WN=hhFGk0semwhdE+ODz3iyv-TroMQ@mail.gmail.com That's always seemed counter to my expectations. Am I just misunderstanding? Tom's comment seemed to confirm what Pavel was saying. I know STRICT can prevent inlining (unfortunately, though necessarily), but it seems inexplicable that IMMUTABLE should. If it can, then the documentation is wrong. Which is it? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndquadrant.com> writes: > The docs say: > "For best optimization results, you should label your functions with the > strictest volatility category that is valid for them." Yeah ... > ... but I recall discussion here suggesting that in fact IMMUTABLE > functions may not be inlined where you'd expect, e.g. > http://www.postgresql.org/message-id/CAFj8pRBF3Qr7WtQwO1H_WN=hhFGk0semwhdE+ODz3iyv-TroMQ@mail.gmail.com The reason that case behaved surprisingly was exactly that the user had violated the above bit of documentation, ie, he'd marked the function *incorrectly* as being immutable when in fact its contained functions were only stable. > I know STRICT can prevent inlining (unfortunately, though necessarily), > but it seems inexplicable that IMMUTABLE should. I don't see why you find that inexplicable. If the planner were to inline this function, it would then fail to reduce a call with constant argument to a constant, which is presumably what the user desires from marking it immutable (questions of correctness in the face of timezone changes notwithstanding). Just as we "keep the wrapper on" when it's necessary to hide possible non-strictness of the body of a function, we must do so when inlining would raise the visible volatility of an expression. It's true that the above-quoted bit of advice presumes that you correctly identify the "strictest volatility category that is valid" for a given function. If you're too lazy or uninformed to do that, it might be better to leave the settings at defaults (volatile/nonstrict) and hope the planner can figure out that it's safe to inline anyway. regards, tom lane
On 06/30/2014 11:49 PM, Tom Lane wrote: > Craig Ringer <craig@2ndquadrant.com> writes: >> The docs say: > >> "For best optimization results, you should label your functions with the >> strictest volatility category that is valid for them." > > Yeah ... > >> ... but I recall discussion here suggesting that in fact IMMUTABLE >> functions may not be inlined where you'd expect, e.g. >> http://www.postgresql.org/message-id/CAFj8pRBF3Qr7WtQwO1H_WN=hhFGk0semwhdE+ODz3iyv-TroMQ@mail.gmail.com > > The reason that case behaved surprisingly was exactly that the user had > violated the above bit of documentation, ie, he'd marked the function > *incorrectly* as being immutable when in fact its contained functions > were only stable. Yes, I realise that's the case with this particular incident. It's the more general case I'm interested in - whether this can be true in general, not just when the user does something dumb. It sounds like you're saying that the behaviour observed here is specific to cases where the user incorrectly identifies the function volatility. In which case we don't care, that's fine, no problem here. My concern was only with whether the advice that the highest volatility category should be used is always true for *correct* immutable functions too. >> I know STRICT can prevent inlining (unfortunately, though necessarily), >> but it seems inexplicable that IMMUTABLE should. > > I don't see why you find that inexplicable. If the planner were to > inline this function, it would then fail to reduce a call with constant > argument to a constant, which is presumably what the user desires from > marking it immutable (questions of correctness in the face of timezone > changes notwithstanding). Just as we "keep the wrapper on" when it's > necessary to hide possible non-strictness of the body of a function, > we must do so when inlining would raise the visible volatility of an > expression. If the input is constant, then clearly it should be evaluated and a constant substituted. If it _isn't_ a constant input, then why would STRICT inline when IMMUTABLE doesn't? > It's true that the above-quoted bit of advice presumes that you correctly > identify the "strictest volatility category that is valid" for a given > function. If you're too lazy or uninformed to do that, it might be > better to leave the settings at defaults (volatile/nonstrict) and hope > the planner can figure out that it's safe to inline anyway. I was unaware that the planner made any attempt to catch users' errors in marking the strictness of functions. I thought it pretty much trusted the user not to lie about the mutability of functions invoked indirectly. I'm not really sure where in the inlining code to look to figure that out. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndquadrant.com> writes:
> I was unaware that the planner made any attempt to catch users' errors
> in marking the strictness of functions. I thought it pretty much trusted
> the user not to lie about the mutability of functions invoked
> indirectly. I'm not really sure where in the inlining code to look to
> figure that out.
It's in optimizer/util/clauses.c:
/*
* Additional validity checks on the expression. It mustn't return a set,
* and it mustn't be more volatile than the surrounding function (this is
* to avoid breaking hacks that involve pretending a function is immutable
* when it really ain't). If the surrounding function is declared strict,
* then the expression must contain only strict constructs and must use
* all of the function parameters (this is overkill, but an exact analysis
* is hard).
*/
if (expression_returns_set(newexpr))
goto fail;
if (funcform->provolatile == PROVOLATILE_IMMUTABLE &&
contain_mutable_functions(newexpr))
goto fail;
else if (funcform->provolatile == PROVOLATILE_STABLE &&
contain_volatile_functions(newexpr))
goto fail;
As the comment says, this wasn't really coded with an eye towards
"catching user error". Rather, there are known use-cases where people
intentionally use SQL wrapper functions to lie about the mutability
of some underlying function; inlining would expose the truth of the
matter and thus defeat such hacks. Now I'd be the first to agree
that this isn't a terribly high-performance way of doing that, but
the point here was to not change the behavior that existed before
SQL inlining did.
regards, tom lane
On Mon, Jun 30, 2014 at 9:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Craig Ringer <craig@2ndquadrant.com> writes: >> I was unaware that the planner made any attempt to catch users' errors >> in marking the strictness of functions. I thought it pretty much trusted >> the user not to lie about the mutability of functions invoked >> indirectly. I'm not really sure where in the inlining code to look to >> figure that out. > > It's in optimizer/util/clauses.c: > > /* > * Additional validity checks on the expression. It mustn't return a set, > * and it mustn't be more volatile than the surrounding function (this is > * to avoid breaking hacks that involve pretending a function is immutable > * when it really ain't). If the surrounding function is declared strict, > * then the expression must contain only strict constructs and must use > * all of the function parameters (this is overkill, but an exact analysis > * is hard). > */ > if (expression_returns_set(newexpr)) > goto fail; > > if (funcform->provolatile == PROVOLATILE_IMMUTABLE && > contain_mutable_functions(newexpr)) > goto fail; > else if (funcform->provolatile == PROVOLATILE_STABLE && > contain_volatile_functions(newexpr)) > goto fail; > > As the comment says, this wasn't really coded with an eye towards > "catching user error". Rather, there are known use-cases where people > intentionally use SQL wrapper functions to lie about the mutability > of some underlying function; inlining would expose the truth of the > matter and thus defeat such hacks. Now I'd be the first to agree > that this isn't a terribly high-performance way of doing that, but > the point here was to not change the behavior that existed before > SQL inlining did. some points: *) there are several cases that look superficially immutable to the user but are really stable. Mostly this comes up with date time functions because of the database dependency. The issue I have with the status quo is that the server punishes you by mis-decorating the function (it gets treaded as volatile). *) some formulations of functions like to_char() are immutable depending on arguments (julian day for example). so if the user wraps this for purposes of indexing and then uses that same function for querying, the operation is non inlineable. *) unless you really know your stuff server inlining is completely abstracted from you, except in terms of performance Adding up the above, the way things work today is kind of a pain -- in may ways I feel that if I mark a function IMMUTABLE, the server should not overrule me. If that argument doesn't hold water, then server should at least tell you when a function is inlineable -- perhaps via \df+. merlin