Thread: BUG #5688: ALTER TABLE ALTER col TYPE newtype fails if col is named in an UPDATE OF col trigger

The following bug has been logged online:

Bug reference:      5688
Logged by:          Daniel Grace
Email address:      dgrace@wingsnw.com
PostgreSQL version: 9.0.0
Operating system:   Windows XP 32-bit
Description:        ALTER TABLE ALTER col TYPE newtype fails if col is named
in an UPDATE OF col trigger
Details:

Given the following state:

CREATE TABLE foo (
    bar TEXT,
        baz TEXT
);

CREATE OR REPLACE FUNCTION foo_trigger_proc() RETURNS TRIGGER LANGUAGE
PLPGSQL AS $$BEGIN RETURN NEW; END$$;
CREATE TRIGGER foo_trigger BEFORE INSERT OR UPDATE OF bar ON foo FOR EACH
ROW EXECUTE PROCEDURE foo_trigger_proc();

The following happens:

> ALTER TABLE foo ALTER bar TYPE VARCHAR;
Fails:
ERROR: unexpected object depending on column: trigger foo_trigger on table
foo
SQL state: XX000

> ALTER TABLE foo ALTER baz TYPE VARCHAR;
Succeeds (because baz is not named in the trigger)

> ALTER TABLE foo DROP bar;
Correctly produces an error message:
ERROR:  cannot drop table foo column bar because other objects depend on it
DETAIL:  trigger foo_trigger on table foo depends on table foo column bar

> ALTER TABLE foo DROP bar CASCADE;
Correctly removes bar and foo_trigger

ALTER TABLE foo RENAME bar and other variations of variations of ALTER
column seem to function correctly.

"Daniel Grace" <dgrace@wingsnw.com> writes:
> Description:        ALTER TABLE ALTER col TYPE newtype fails if col is named
> in an UPDATE OF col trigger

Hmm, definitely a missed case (and defensive programming saves the day
again).

There are actually two cases as of 9.0 where a trigger could depend on a
column:
    * UPDATE OF said column, as in your example
    * column is used in trigger's WHEN clause

In the first case we could just allow the ALTER to proceed, but in the
second case we'd have to adjust the WHEN expression for the column's new
type, which is something there's not code for now.  And it doesn't look
like we can easily tell which case applies.  I think what we'll have to
do here is just throw a FEATURE_NOT_SUPPORTED error, and maybe add a
TODO item to come back to improve that someday.  (Since you can just
drop the trigger and re-add it after the ALTER, a nicer fix doesn't seem
like a high priority IMO.)

Thanks for the report!  It's too late for 9.0.1 but we'll do something
about this for 9.0.2.

            regards, tom lane