Re: stable for each row before insert trigger

Luca, I also read this section before ask the question.

> 18 окт. 2019 г., в 10:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> написал(а):
>
> =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <splarv(at)ya(dot)ru> writes:
>> According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is somehow useful with trigger functions, for instance mentioned that the AFTER INSERT trigger should be VOLATILE. The question is how this words affect a for each row before 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, but not 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 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.

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 important negative side effects. I observed 3% benefit. IMMUTABLE trigger is slightly slower then STABLE, but I am not sure, too low difference.