Fix inherited constraint loss in ALTER COLUMN SET EXPRESSION

From: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Fix inherited constraint loss in ALTER COLUMN SET EXPRESSION
Date: 2026-09-08 09:14:56
Message-ID: 1A7CBF99-628F-4A75-A897-135BC410C6F0@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

While working on another task, I encountered an issue where SET EXPRESSION on a child table causes inherited CHECK constraints to be lost, allowing rows that violate those constraints to be inserted. Inherited NOT NULL constraints are also removed from pg_constraint, although NOT NULL enforcement remains in place. Partitions are also affected.

Here is a simple repro:
```
evantest=# create table p (a int, b int generated always as (a*2) virtual, check (b<100));
CREATE TABLE
evantest=# create table c () inherits (p);
CREATE TABLE
evantest=# insert into c (a) values (1000);
ERROR: new row for relation "c" violates check constraint "p_b_check"
DETAIL: Failing row contains (1000, virtual).
evantest=# alter table c alter b set expression as (a*3);
ALTER TABLE
evantest=# insert into c (a) values (1000);
INSERT 0 1
```

The current implementation drops dependent constraints and assumes that inherited constraints will be recreated when the parent constraint is added recursively. However, when SET EXPRESSION is applied directly to a child table, no parent rebuild takes place, leaving the child's inherited constraints missing.

The attached patch fixes this by preserving the constraints instead of rebuilding them. Enforced, validated CHECK constraints are rechecked against the new generation expression, while NOT NULL constraints use the existing verification paths. Constraints marked NOT VALID remain unvalidated.

Is this a PG19-new bug? Not really. PG19 allows SET EXPRESSION on virtual generated columns with CHECK constraints, but PG17 introduced SET EXPRESSION for stored generated columns. This fix covers both virtual and stored generated columns, so ideally it can be back-patched to PG17.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachment Content-Type Size
v1-0001-Preserve-CHECK-and-NOT-NULL-constraints-during-SE.patch application/octet-stream 30.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Clemenza Zhang 2026-09-08 09:23:30 Re: Bug: Whole-row var in indexes corrupts indexes after DDL
Previous Message Daniel Gustafsson 2026-09-08 09:12:40 Re: Offline data checksum changes can cause incorrect checksum state on standbys