Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object

hi.

CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
  BEGIN
    RETURN NULL;
  END
$$ language plpgsql;

create table main_table(a int);
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH ROW
WHEN (new.a > 0)
EXECUTE PROCEDURE dummy_trigger();

ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --error
ALTER TABLE main_table DROP COLUMN a; --error

Dropping a column or changing its data type will fail if the column is
referenced in a trigger’s WHEN clause, that's the current behavior.
I think we should expand that to a whole-row reference WHEN clause in trigger.

DROP TRIGGER before_ins_stmt_trig ON main_table;
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH ROW
WHEN (new is null)
EXECUTE PROCEDURE dummy_trigger();
ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --expect to error
ALTER TABLE main_table DROP COLUMN a;  --expect to error

new summary:
For (constraints, indexes, policies, triggers) that contain whole-row
references:
ALTER TABLE DROP COLUMN [CASCADE] will drop these objects too.

ALTER COLUMN SET DATA TYPE will error out because whole-row–dependent objects
exist.



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