Re: Virtual generated columns

On Fri, Nov 29, 2024 at 6:01 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> The purpose of check_modified_virtual_generated() for trigger functions
> written in C. The prevent someone from inserting real values into the
> trigger tuples, because they would then be processed by the rest of the
> system, which would be incorrect.
>
> Higher-level languages such as plpgsql should handle that themselves, by
> preventing setting generated columns in trigger functions. The presence
> of check_modified_virtual_generated() is still a backstop for those, but
> shouldn't really be necessary.

please check the attached patch.
* remove check_modified_virtual_generated.
* using heap_modify_tuple_by_cols in ExecBRInsertTriggers, ExecBRUpdateTriggers
to overwrite virtual generated columns value to null.

and it's not complicated.
so that trigger behavior for stored and virtual will be more aligned

-------------------------------
I think contrib module: spi-autoin can be used to test triggers (c
language) before rows behavior
for the generated columns (stored, virtual).

for example (copied from contrib/spi/autoinc.example)

DROP SEQUENCE next_id;
CREATE SEQUENCE next_id START -2 MINVALUE -2;
CREATE TABLE id_gen_stored (id int4 GENERATED ALWAYS AS (2) stored,idesc text);

CREATE TRIGGER ids_nextids
BEFORE INSERT OR UPDATE ON id_stored
FOR EACH ROW
EXECUTE PROCEDURE autoinc (id, next_id);

INSERT INTO id_gen_stored VALUES (default, 'hello');
select * from id_gen_stored;
UPDATE id_gen_stored SET id = default, idesc = 'world' ;
select * from id_gen_stored;

then we can validate this sentence in trigger.sgml:
"""
Changes to the value of a generated column in a
<literal>BEFORE</literal> trigger are ignored and will be overwritten.
"""
for c language triggers.