ALTER TABLE DROP COLUMN: Handling Whole-Row Referenced Objects

Core Problem

PostgreSQL has a fundamental gap in its dependency tracking system when dealing with whole-row Var references in constraints, indexes, policies, and triggers. When an expression references a table's composite row type (e.g., ts = ROW(1,1,1) or ts IS NULL), the system does not record individual column-level dependencies in pg_depend. This means that ALTER TABLE DROP COLUMN fails to cascade the drop to these dependent objects, leaving the table in an inconsistent and unusable state.

The Architectural Root Cause

The issue traces directly to find_expr_references_walker in the dependency recording code. When encountering a whole-row Var (varattno = 0), it deliberately records no column-level dependencies, relying instead on a whole-table dependency from the rangetable entry. The comment in the code explicitly acknowledges this design choice:

/* A whole-row Var references no specific columns, so adds no new dependency. */

This was a reasonable simplification for most cases, but it creates a semantic gap: when you drop column a from table ts, the system doesn't know that constraint CHECK (ts = ROW(1,1,1)) implicitly depends on column a (because the whole-row reference includes all columns).

Concrete Manifestation

CREATE TABLE ts (a int, c int, b int
    CONSTRAINT cc CHECK((ts = ROW(1,1,1))));
ALTER TABLE ts DROP COLUMN a;
-- cc is NOT dropped, but now:
INSERT INTO ts VALUES (2, 3);
-- ERROR: cannot compare record types with different numbers of columns

The table becomes effectively broken — no inserts are possible because the remaining constraint references a row type that no longer matches the table's actual structure.

Proposed Solution (Evolution Through v1–v9)

v1: Direct Object Deletion

The initial approach added whole-row-referencing objects directly to the deletion list via add_exact_object_address. This was crude but functional.

v2: Dependency Recording Approach (Key Design Shift)

Rather than bypassing the dependency system, v2 takes the architecturally superior approach of explicitly recording the missing dependencies before deletion. In ATExecDropColumn, before calling performMultipleDeletions, the patch calls recordDependencyOn to create the column→object dependency entries that find_expr_references_walker deliberately omits. This allows the existing deletion machinery to handle cascading correctly.

v3–v5: Expanding Scope

The patch expanded to cover:

1. CHECK constraints with whole-row references
2. Index expressions (e.g., (ts IS NULL))
3. Index predicates (e.g., WHERE ts IS NOT NULL)
4. Row security policies (which can contain subqueries, complicating detection)
5. ALTER COLUMN SET DATA TYPE (errors out rather than silently corrupting)

v6–v7: Triggers with WHEN Clauses

Discovery that trigger WHEN clauses referencing NEW or OLD as whole-row also need handling. This adds a fourth object class (triggers) to the scope.

v8–v9: Refinement and Consistency

Code refactoring, deduplication, and alignment with a related patch for ALTER COLUMN SET EXPRESSION ([CF patch 6755]).

Key Technical Design Decisions

1. Record Dependencies vs. Direct Deletion

The v2+ approach of recording missing dependencies first (then relying on performMultipleDeletions) is architecturally cleaner than directly adding objects to a deletion list. It means the deletion logic doesn't need special cases — the standard dependency-based cascade handles everything.

2. pull_varattnos Limitations

The patch uses pull_varattnos to detect whole-row references (varattno = 0 maps to FirstLowInvalidHeapAttributeNumber in the bitmapset). However, pull_varattnos cannot recurse into SubLink nodes (unplanned subqueries). This forced a split:

• 0001: Handles indexes and CHECK constraints (no subqueries allowed in these)
• 0002: Handles policies and triggers (may contain subqueries, requires alternative detection using vartype matching against the relation's row type OID)

3. ALTER SET DATA TYPE: Error vs. Auto-fix

For ALTER COLUMN SET DATA TYPE, the patch chose to error out rather than silently proceed. This is because changing a column's type with a whole-row constraint present would cause runtime errors like "cannot compare dissimilar column types bigint and integer at record column 3" — cryptic errors far from the root cause. The explicit error with a HINT to drop the constraint first is far more user-friendly.

4. CommandCounterIncrement Placement

A CommandCounterIncrement() is placed after recordDependencyOn calls and before performMultipleDeletions. This ensures the newly-recorded pg_depend tuples are visible to the deletion machinery that will scan pg_depend to find cascading targets.

5. Policy Whole-Row Detection

For policies, since expressions can reference arbitrary relations via subqueries, the patch cannot simply check polrelid — it must use pg_depend to properly track which columns are referenced. The whole-row detection works by obtaining the table's composite type OID and recursively checking each Var's vartype.

Behavioral Summary

Open Design Questions

1. Memory management: Reviewers noted pull_varattnos allocates Bitmapsets that are never freed in loops, requiring bms_free() calls.
2. Interaction with ALTER COLUMN SET EXPRESSION: The v9 patch aligns its code style with CF patch 6755 which handles whole-row dependencies for SET EXPRESSION, suggesting eventual consolidation.
3. Whether TRIGGER_FOR_ROW should gate tgqual inspection: A reviewer suggested checking row-level trigger status before processing the WHEN clause, though the author preferred unconditional checking.