Luca, I also read this section before ask the question. > 18 окт. 2019 г., в 10:15, Tom Lane <t...@sss.pgh.pa.us> написал(а): > > =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <spl...@ya.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.
