let ALTER COLUMN SET DATA TYPE cope with trigger dependency

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: let ALTER COLUMN SET DATA TYPE cope with trigger dependency
Date: 2025-09-29 12:24:00
Message-ID: CACJufxGkqYrmwMdvUOUPet0443oUTgF_dKCpw3TfJiutfuywAQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.

demo:
CREATE TABLE main_table (a int, b int);
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'',
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;';
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');

ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8;
ERROR: cannot alter type of a column used in a trigger definition
DETAIL: trigger modified_a on table main_table depends on column "a"

With the attached patch, the previous error will no longer occur.
Foreign key–related internal triggers are not directly dependent on the relation
itself; instead, they depend directly on the constraint.
Therefore, we don't need to worry about internal triggers in this context.

v1-0001: "refactor CreateTrigger and CreateTriggerFiringOn".
used also in https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
v1-0002, the actual implementation.

Attachment Content-Type Size
v1-0001-refactor-CreateTrigger-and-CreateTriggerFiringOn.patch text/x-patch 23.1 KB
v1-0002-let-ALTER-COLUMN-SET-DATA-TYPE-cope-with-trigger-dependency.patch text/x-patch 26.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Florents Tselai 2025-09-29 12:34:18 Re: Add jsonb_translate(jsonb, from, to)
Previous Message Daniel Gustafsson 2025-09-29 11:27:49 Re: Doc compilation fails with a recent xmllint