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

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object
Date: 2025-09-09 03:12:36
Message-ID: CACJufxFMqpraCreGKTWWB7cyPAYP+yehwTMFm-CSx+rzWdHf+A@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.
I found a new way to solve this problem.

CREATE TABLE ts (a int, c int, b int
constraint cc check((ts = ROW(1,1,1))),
constraint cc1 check((ts.a = 1)));

for constraint cc, there is no extra dependency between column a and
constraint cc.
see find_expr_references_walker below comments:

/*
* A whole-row Var references no specific columns, so adds no new
* dependency. (We assume that there is a whole-table dependency
* arising from each underlying rangetable entry. While we could
* record such a dependency when finding a whole-row Var that
* references a relation directly, it's quite unclear how to extend
* that to whole-row Vars for JOINs, so it seems better to leave the
* responsibility with the range table. Note that this poses some
* risks for identifying dependencies of stand-alone expressions:
* whole-table references may need to be created separately.)
*/
Ideally, for constraint "cc", there should be three pg_depend entries
corresponding to column a, column b, and column c, but those entries are
missing, but we didn't.

so, in ATExecDropColumn, instead of adding another object to the deletion
list (``add_exact_object_address(&object, addrs)``) like what we did v1,
we first call recordDependencyOn to explicitly record the dependency between
constraint cc and column a, and then rely on performMultipleDeletions to handle
the deletion properly

demo:
CREATE TABLE ts (a int, c int, b int
constraint cc check((ts = ROW(1,1,1))),
constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;
CREATE POLICY p1 ON ts USING (ts >= ROW(1,1,1));
CREATE POLICY p2 ON ts USING (ts.a = 1);

ALTER TABLE ts DROP COLUMN a CASCADE;
will drop above all indexes, constraints and policies on the table ts.

Attachment Content-Type Size
v2-0001-ALTER-TABLE-DROP-COLUMN-drop-wholerow-referenced-object.patch text/x-patch 15.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2025-09-09 03:58:32 Re: Remove traces of long in dynahash.c
Previous Message Ashutosh Bapat 2025-09-09 03:11:55 Re: Query Performance Degradation Due to Partition Scan Order – PostgreSQL v17.6