Обсуждение: identity column behavior in WHEN condition for BEFORE EACH ROW trigger

Поиск
Список
Период
Сортировка

identity column behavior in WHEN condition for BEFORE EACH ROW trigger

От
Suraj Kharage
Дата:
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.

Re: identity column behavior in WHEN condition for BEFORE EACH ROW trigger

От
Suraj Kharage
Дата:
 
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.
 
Also, I think it is breaking the OVERRIDING SYSTEM VALUE clause in INSERT statement. i.e: without this clause, can insert the modified value from trigger in identity column. I don't find any document reference for this behavior.

Thoughts?

--
--

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

Re: identity column behavior in WHEN condition for BEFORE EACH ROWtrigger

От
Peter Eisentraut
Дата:
On 2019-10-03 16:08, Suraj Kharage wrote:
> 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().

> 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.

This is per SQL standard: Identity columns are assigned before triggers,
generated columns are computed after BEFORE triggers.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services