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

On Sat, 27 Dec 2025 at 08:01, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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.
>

Hi!
I did take a look at v7.

In 0001:

> + indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
> + NULL, 1, &skey);
> + while (HeapTupleIsValid(htup = systable_getnext(indscan)))
> + {
> + Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
> +
> + /* add index's OID to result list */
> + indexlist = lappend_oid(indexlist, index->indexrelid);
> + }
> + systable_endscan(indscan);
> +
> + table_close(pg_index, AccessShareLock);
> +
> + foreach_oid(indexoid, indexlist)
> + {

Hmm, why is this not just a one cycle? Also, not sure how many
relations can be returned by pg_index scan. Maybe it is worth adding
CHECK_FOR_INTERRUPTS() here?

--
Best regards,
Kirill Reshke