sql: incorrect handling for computed columns in row-level BEFORE triggers

[DrewKimball]

Currently, row-level BEFORE triggers are projected after computed columns. This means that the trigger will be able to "see" and directly modify the computed values, and that modifications made by the trigger won't be visible in the computed column.

Contrast this with Postgres:

Generated columns are, conceptually, updated after BEFORE triggers have run. Therefore, changes made to base columns in a >BEFORE trigger will be reflected in generated columns. But conversely, it is not allowed to access generated columns in BEFORE >triggers.

Example with direct modification of the computed column.

postgres=# create table t (x int primary key, y int generated always as (x * 10) stored);
CREATE TABLE
postgres=# create function f() returns trigger language plpgsql as $$ begin raise notice '%', new; new.y := -1; return new; end $$;
CREATE FUNCTION
postgres=# create trigger trig before insert on t for each row execute function f();
CREATE TRIGGER
postgres=# insert into t values (1), (2);
NOTICE:  (1,)
NOTICE:  (2,)
INSERT 0 2
postgres=# select * from t;
 x | y
---+----
 1 | 10
 2 | 20
(2 rows)

Example with modification of a referenced column.

postgres=# create function f() returns trigger language plpgsql as $$ begin raise notice '%', new; new.x := new.x + 10; return new; end $$;
CREATE FUNCTION
postgres=# create trigger trig before insert on t for each row execute function f();
CREATE TRIGGER
postgres=# insert into t values (1), (2);
NOTICE:  (1,)
NOTICE:  (2,)
INSERT 0 2
postgres=# select * from t;
 x  |  y
----+-----
 11 | 110
 12 | 120
(2 rows)

Jira issue: CRDB-43620

[blathers-crl]

Based on the specified backports for linked PR #133328, I applied the following new label(s) to this issue: branch-release-24.3. Please adjust the labels as needed to match the branches actually affected by this issue, including adding any known older branches.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}