Thread: BUG #17559: Inconsistent visibility in trigger function

The following bug has been logged on the website:

Bug reference:      17559
Logged by:          Nick Barnes
Email address:      nickbarnes01@gmail.com
PostgreSQL version: 14.4
Operating system:   Windows 10
Description:

Hi,

I stumbled across some odd behaviour which appears to contradict the docs.
According to
https://www.postgresql.org/docs/current/trigger-datachanges.html :
>  Functions that are declared STABLE or IMMUTABLE will not see changes made
by the calling command in any case.

Testing this with a stable deferred trigger:

CREATE TABLE t (id int);

CREATE FUNCTION trg_stable() RETURNS TRIGGER LANGUAGE plpgsql STABLE
AS $$ BEGIN
  RAISE NOTICE 'trg_stable %', ARRAY(SELECT * FROM t);
  RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER trg_stable   
AFTER INSERT ON t
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION trg_stable();

INSERT INTO t VALUES (1);
> NOTICE:  trg_stable {}
> INSERT 0 1

So far so good. However:

CREATE FUNCTION trg_volatile() RETURNS TRIGGER LANGUAGE plpgsql VOLATILE
AS $$ BEGIN
  RAISE NOTICE 'trg_volatile %', ARRAY(SELECT * FROM t);
  RETURN NULL;
END $$;

CREATE TRIGGER trg_volatile 
AFTER INSERT ON t
FOR EACH ROW
EXECUTE FUNCTION trg_volatile();

INSERT INTO t VALUES (2);
> NOTICE:  trg_volatile {1,2}
> NOTICE:  trg_stable {1,2}
> INSERT 0 1

i.e. after adding a volatile non-deferred trigger to the same table, the
stable trigger's snapshot now contains the newly-inserted row, which is
surprising to say the least.

With both triggers deferred, or with both triggers immediate, they appear to
behave as documented (regardless of which fires first).

Is it a bug?

Thanks,
Nick Barnes