From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: nickbarnes01@gmail.com
Subject: BUG #17559: Inconsistent visibility in trigger function
Date: Wed, 27 Jul 2022 07:32:46 +0000
Message-ID: <17559-73d42ff8145d1ce0@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, nickbarnes01@gmail.com
Subject: Re: BUG #17559: Inconsistent visibility in trigger function
In-Reply-To: <17559-73d42ff8145d1ce0@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox