Обсуждение: stable for each row before insert trigger
Hi all. According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for instancementioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each rowbefore insert trigger? Can be some optimisation here?
On 10/17/19 4:31 PM, Олег Самойлов wrote: > Hi all. > > According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for instancementioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each rowbefore insert trigger? Can be some optimisation here? > > https://www.postgresql.org/docs/11/xfunc-volatility.html -- Adrian Klaver adrian.klaver@aklaver.com
Eh, stupid answer. Of cause, I read the documentation. But what about you? Do you know what is a trigger function? (for eachrow before insert) A trigger function don't have parameters, instead it get special variables. OLD and NEW for instance or TG_ARGV[]. If trigger function depends only on NEW is it IMMUTABLE? (With the same NEW it must return the same changed NEW). If triggerfunction makes SELECTs and change only NEW is it can be declared as STABLE? And etc. > 18 окт. 2019 г., в 2:41, Adrian Klaver <adrian.klaver@aklaver.com> написал(а): > > On 10/17/19 4:31 PM, Олег Самойлов wrote: >> Hi all. >> According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for instancementioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each rowbefore insert trigger? Can be some optimisation here? > > https://www.postgresql.org/docs/11/xfunc-volatility.html > > -- > Adrian Klaver > adrian.klaver@aklaver.com
=?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <splarv@ya.ru> writes:
> According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for
instancementioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each
rowbefore insert trigger? Can be some optimisation here?
Where did you read that? There's no optimization that considers the
volatility of trigger functions --- they'll be called exactly when
specified, no more or less.
There are some PLs that might behave differently depending on whether they
think the function is volatile or not, but that's independent of whether
the function is a trigger.
regards, tom lane
On Fri, Oct 18, 2019 at 9:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <splarv@ya.ru> writes: > > According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for instancementioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each rowbefore insert trigger? Can be some optimisation here? > > Where did you read that? There's no optimization that considers the > volatility of trigger functions --- they'll be called exactly when > specified, no more or less. I suspect this sentence could have raised the OP doubt: "For best optimization results, you should label your functions with the strictest volatility category that is valid for them." (from <https://www.postgresql.org/docs/11/xfunc-volatility.html>). However, here <https://www.postgresql.org/docs/11/trigger-datachanges.html> it is specified that: "If your trigger function is written in any of the standard procedural languages, then the above statements apply only if the function is declared VOLATILE. Functions that are declared STABLE or IMMUTABLE will not see changes made by the calling command in any case." which I hope is the answer to the original question. Luca
Luca, I also read this section before ask the question. > 18 окт. 2019 г., в 10:15, Tom Lane <tgl@sss.pgh.pa.us> написал(а): > > =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <splarv@ya.ru> writes: >> According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for instancementioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each rowbefore insert trigger? Can be some optimisation here? > > Where did you read that? There's no optimization that considers the > volatility of trigger functions --- they'll be called exactly when > specified, no more or less. Good to see this. :) But there is somehow optimisation for triggers, which is somehow mentioned in the documentation, butnot clearly defined. https://www.postgresql.org/docs/current/sql-createfunction.html > STABLE indicates .... (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) So, STABLE is inappropriate for such trigger, but is appropriate for BEFORE trigger? Luca correctly pointed to: https://www.postgresql.org/docs/current/trigger-datachanges.html > If your trigger function is written in any of the standard procedural languages, then the above statements apply only ifthe function is declared VOLATILE. Functions that are declared STABLE or IMMUTABLE will not see changes made by the callingcommand in any case. So will be good put inside right section https://www.postgresql.org/docs/current/xfunc-volatility.html Exact definition how "VOLATILE, STABLE, IMMUTABLE" affect a trigger function. For instance, I expect that the FOR EACH ROW BEFORE trigger marked as STABLE will be faster than VOLATILE without importantnegative side effects. I observed 3% benefit. IMMUTABLE trigger is slightly slower then STABLE, but I am not sure,too low difference.
On Fri, Oct 18, 2019 at 11:18 AM Олег Самойлов <splarv@ya.ru> wrote: > > STABLE indicates .... (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) > > So, STABLE is inappropriate for such trigger, but is appropriate for BEFORE trigger? > Well, a before trigger will not see data changes in any way, so I read it as "it does not matter for before triggers". And I would speculate that, being in a trigger, the function is invoked every time, so there should not be any caching that produces performance boosts. > For instance, I expect that the FOR EACH ROW BEFORE trigger marked as STABLE will be faster than VOLATILE without importantnegative side effects. I observed 3% benefit. IMMUTABLE trigger is slightly slower then STABLE, but I am not sure,too low difference. It could be faster, but I would not speculate on that rather than an inling of the code. Luca