identity column behavior in WHEN condition for BEFORE EACH ROW trigger - Mailing list pgsql-hackers

From Suraj Kharage
Subject identity column behavior in WHEN condition for BEFORE EACH ROW trigger
Date
Msg-id CAF1DzPUp2c0F7YxH22VJzEsOoEMdJrq-Yt-3TcDYQVSZ0NT02g@mail.gmail.com
Whole thread Raw
Responses Re: identity column behavior in WHEN condition for BEFORE EACH ROW trigger
Re: identity column behavior in WHEN condition for BEFORE EACH ROWtrigger
List 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 by date:

Previous
From: Euler Taveira
Date:
Subject: Re: Regarding extension
Next
From: Robert Haas
Date:
Subject: Re: WIP: Generic functions for Node types using generated metadata