PostgreSQL insert or update trigger function volatility category

Question

Assume, i have 2 tables in my DB (postgresql-9.x)

CREATE TABLE FOLDER (
    KEY BIGSERIAL PRIMARY KEY,
    PATH TEXT,
    NAME TEXT
);
CREATE TABLE FOLDERFILE (
    FILEID BIGINT,
    PATH TEXT,
    PATHKEY BIGINT
);

I automatically update FOLDERFILE.PATHKEY from FOLDER.KEY whenever i insert into or update FOLDERFILE:

CREATE OR REPLACE FUNCTION folderfile_fill_pathkey() RETURNS trigger AS $$
DECLARE
  pathkey bigint;
  changed boolean;
BEGIN
  IF tg_op = 'INSERT' THEN
     changed := TRUE;
    ELSE IF old.FILEID != new.FILEID THEN
       changed := TRUE;
    END IF;
  END IF;
  IF changed THEN
     SELECT INTO pathkey key FROM FOLDER WHERE PATH = new.path;
     IF FOUND THEN
       new.pathkey = pathkey;
     ELSE
       new.pathkey = NULL;
     END IF;
  END IF;
  RETURN new;
END
$$ LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER folderfile_fill_pathkey_trigger AFTER INSERT OR UPDATE 
ON FOLDERFILE FOR EACH ROW EXECUTE PROCEDURE fcliplink_fill_pathkey();

So the question is about function folderfile_fill_pathkey() volatility. Documentations says

Any function with side-effects must be labeled VOLATILE

But as far as i understand – this function does not change any data in the tables it rely on, so i can mark this function as IMMUTABLE. It that correct?

Would there be any problem with IMMUTABLE trigger function if I bulk-insert many rows into FOLDERFILE within the same transaction, like:

BEGIN;
INSERT INTO FOLDERFILE ( ... );
...
INSERT INTO FOLDERFILE ( ... );
COMMIT;

Hypozeugma

This "function" (strictly speaking, its body) is not IMMUTABLE, it's just STABLE (it selects from your FOLDER table, which can change over time: your function does not guarantee the same results for the same "parameters") -- but, it's a trigger, not a classical function: function volatility only matters for query optimizations, but it will only cause you troubles, if you optimize triggers, f.ex. STABLE - It is inappropriate for AFTER triggers that wish to query rows modified by the current command. – Omaromara

Thanks @pozs! I did not actually fully understood the difference between IMMUTABLE and STABLE, so I'll have to fix few things now in my DB. So your comment is greatly appreciated. – Hypozeugma

@pozs, so as far as i got it – making my function STABLE instead of VOLATILE will not give me any performance advantages? Would be nice also if you make it an answer so i can accept it – otherwise i'll have to do it myself. – Hypozeugma

it's hard to predict, if STABLE actually perform better, or not. Query optimization depends on a lot of things. (In your case -- i.e. you do not query the trigger's target table FOLDERFILE inside the function -- it should not matter). But, if you want to be precise, you should test both variants with real data. – Omaromara

ok, thanks, i'll do benchmark later and post result here – Hypozeugma

Answer 1

Firstly, as @pozs already pointed out, the function definition you have provided is most definitely STABLE rather than IMMUTABLE since it performs database look-ups. This means that the result is not simply derived from the input parameters (as IMMUTABLE would suggest), but also from the data stored in your FOLDER table (which is bound to change). As per the documentation:

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc.

Secondly, adding stability modifiers (IMMUTABLE/STABLE/VOLATILE) to your trigger functions serves an illustrative purpose at best, since AFAIK PostgreSQL doesn't actually perform any planning that would warrant their use. The following post from the pgsql-hackers mailing list seems to support my claim:

Volatility is a complete no-op for a trigger function anyway, as are other planner parameters such as cost/rows, because there is no planning involved in trigger calls.

To sum up: you're probably better off avoiding the stability keywords in your trigger(!) procedures for now, since including them seems to add little to no benefit but entails several unexpected caveats/pitfalls (see the end of @pozs's first comment).

Kazukokb