Обсуждение: let ALTER COLUMN SET DATA TYPE cope with trigger dependency

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.

hi.

v2-0001, v2-0002 is the same as [1].
The parameters relOid and refRelOid are removed from the CreateTrigger function
and instead added to the CreateTrigStmt structure.

The main reason is to avoid repeated name lookups.
In fact, we already did this in CreateTriggerFiringOn, see below code snippet.
If the OID is valid, use it preferentially; otherwise, fall back to resolving
the relation via the RangeVar.

```
    if (OidIsValid(relOid))
        rel = table_open(relOid, ShareRowExclusiveLock);
    else
        rel = table_openrv(stmt->relation, ShareRowExclusiveLock);

        if (OidIsValid(refRelOid))
        {
            LockRelationOid(refRelOid, AccessShareLock);
            constrrelid = refRelOid;
        }
        else if (stmt->constrrel != NULL)
            constrrelid = RangeVarGetRelid(stmt->constrrel, AccessShareLock,
                                           false);
```

v2-0003: is the actual implementation.
The general workflow would be as follows: collect all affected triggers,
retrieve their definitions, drop the existing triggers, and then recreate them.
Any comments associated with the triggers will also be dropped and recreated.

[1]: https://postgr.es/m/CACJufxFu7Y4FhVkaKT2Kaj8ym2T5TcwN93cR_6h4x66iLrSZ-Q@mail.gmail.com


--
jian
https://www.enterprisedb.com/