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/
v1-0001-Preserve-CHECK-and-NOT-NULL-constraints-during-SE.patch
Description: Binary data
