Hi.
Reproducer:
drop table if exists t;
CREATE TABLE t (
a int,
b int GENERATED ALWAYS AS (NULLIF(a, 1)) STORED NOT NULL,
constraint cc check (b > 0)) PARTITION BY RANGE (a);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (10) TO (20);
ALTER TABLE tp_0_1 ALTER COLUMN b SET EXPRESSION AS (a);
-- expect two row, now nothing
select conname, contype from pg_constraint where conrelid = 'tp_0_1'::regclass;
This issue has existed since PG17.
ATPostAlterTypeCleanup only generates the ALTER TABLE ADD CONSTRAINT command for
the root (parent) table, not for the partitions themselves.
For a partition table, it only drops the old constraint without
rebuilding it: because a partition's pg_constraint.conislocal is false.
See the comments in ATPostAlterTypeCleanup below:
/*
* If the constraint is inherited (only), we don't want to inject a
* new definition here; it'll get recreated when
* ATAddCheckNNConstraint recurses from adding the parent table's
* constraint. But we had to carry the info this far so that we can
* drop the constraint below.
*/
I don't have a clear idea how to solve this issue other than by erroring out.
It seems non-trivial to drop an inherited constraint, recreate it and
correctly integrate it into the constraint inheritance hierarchy.
I checked src/test/regress/sql/generated_stored.sql, no related tests for it.
--
jian
https://www.enterprisedb.com/
