BUG #17559: Inconsistent visibility in trigger function

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17559: Inconsistent visibility in trigger function
Дата
Msg-id 17559-73d42ff8145d1ce0@postgresql.org
обсуждение исходный текст
Список pgsql-bugs
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


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Kyotaro Horiguchi
Дата:
Сообщение: Re: could not link file in wal restore lines
Следующее
От: Marco Boeringa
Дата:
Сообщение: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?