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 |
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 |