identity column behavior in WHEN condition for BEFORE EACH ROW trigger

Поиск
Список
Период
Сортировка
От Suraj Kharage
Тема identity column behavior in WHEN condition for BEFORE EACH ROW trigger
Дата
Msg-id CAF1DzPUp2c0F7YxH22VJzEsOoEMdJrq-Yt-3TcDYQVSZ0NT02g@mail.gmail.com
обсуждение исходный текст
Ответы Re: identity column behavior in WHEN condition for BEFORE EACH ROW trigger  (Suraj Kharage <suraj.kharage@enterprisedb.com>)
Re: identity column behavior in WHEN condition for BEFORE EACH ROWtrigger  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
Список pgsql-hackers
Hi,

It is been observed that when we define the generated columns in WHEN condition for BEFORE EACH ROW trigger then server throw an error from CreateTrigger().

e.g:
create table bar(a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);

CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $$
BEGIN
NEW.b  = 10;
raise notice 'Before row trigger';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

postgres@78049=#CREATE TRIGGER bar_trigger
BEFORE INSERT ON bar
FOR EACH ROW
WHEN (NEW.b < 8)
EXECUTE FUNCTION test();
2019-10-03 19:25:29.945 IST [78049] ERROR:  BEFORE trigger's WHEN condition cannot reference NEW generated columns at character 68
2019-10-03 19:25:29.945 IST [78049] DETAIL:  Column "b" is a generated column.
2019-10-03 19:25:29.945 IST [78049] STATEMENT:  CREATE TRIGGER bar_trigger
BEFORE INSERT ON bar
FOR EACH ROW
WHEN (NEW.b < 8)
EXECUTE FUNCTION test();
ERROR:  BEFORE trigger's WHEN condition cannot reference NEW generated columns
LINE 4: WHEN (NEW.b < 8)
              ^
DETAIL:  Column "b" is a generated column.


whereas, for identity columns, server allows us to create trigger for same and trigger gets invoked as defined. Is this behavior expected? or we need to restrict the identity columns in such scenario because anyone one override the identity column value in trigger.

e.g:

create table foo(no int, id int  generated always as identity);

CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $$
BEGIN
NEW.id  = 10;
raise notice 'Before row trigger';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_trigger
BEFORE INSERT ON foo
FOR EACH ROW
WHEN (NEW.id < 8)
EXECUTE FUNCTION test();


postgres@78049=#insert into foo values(1);
NOTICE:  Before row trigger
INSERT 0 1
postgres@78049=#select * from foo;
 no | id
----+----
  1 | 10
(1 row)


Thoughts?

--
--

Thanks & Regards, 
Suraj kharage, 
EnterpriseDB Corporation, 
The Postgres Database Company.

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

Предыдущее
От: Euler Taveira
Дата:
Сообщение: Re: Regarding extension
Следующее
От: Robert Haas
Дата:
Сообщение: Re: WIP: Generic functions for Node types using generated metadata