On Fri, Nov 29, 2024 at 6:01 PM Peter Eisentraut <pe...@eisentraut.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.
v10-0001-overwritten-virtual-generated-column-value-to.no-cfbot
Description: Binary data
