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.
