Re: ALTER COLUMN SET EXPRESSION on partitions not work in case of constraint dependencies

Hi.

ALTER TABLE ONLY ALTER COLUMN SET EXPRESSION is not working in some
corner case too:

drop table if exists t, pk cascade;
create table pk(a int primary key);
insert into pk select g from generate_series(1, 10) g;
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 tp1 PARTITION OF t FOR VALUES FROM (1) TO (20);
insert into t values (2), (3);
create index on t(b);
CREATE STATISTICS t_stat_only ON (b is not null) FROM t;
alter table t add foreign key (b) references pk;

ALTER TABLE ONLY t ALTER COLUMN b SET EXPRESSION AS (a + 1);
NOTICE: merging constraint "cc" with inherited definition
ERROR: could not read blocks 0..0 in file "base/5/18933": read only 0
of 8192 bytes
CONTEXT: SQL statement "SELECT fk."b" FROM ONLY "public"."tp1" fk
LEFT OUTER JOIN ONLY "public"."pk" pk ON ( pk."a"
OPERATOR(pg_catalog.=) fk."b") WHERE pk."a" IS NULL AND (fk."b" IS NOT
NULL)"
---------------------------------
If I remove the above ``create index on t(b);``, then the above ALTER
TABLE ONLY SET EXPRESSION
works.
I suspect this is because the transient index is dropped and rebuilt,
making it unusable during the SPI SELECT query while we are still
using it.
(This part I didn't dig deeper).

It appears to be only index dependency and no constraint dependency, ALTER TABLE
ONLY ... SET EXPRESSION may still work. However, this could contradict
the word "ONLY" somehow because we still rebuild the child table's indexes. And
there may be other unknown complications. To avoid unexpected behavior, it would
be better to disallow this case (ALTER TABLE ONLY with only index
dependency) as well.

Summary:
ALTER TABLE ONLY ... SET EXPRESSION should fail if the generated
column has dependencies, because rebuilding dependent objects
contradicts the word "ONLY" and may lead to unintended errors.
Directly using ALTER TABLE ... SET EXPRESSION on a child table should
fail because rebuilding dependent objects while preserving the
inheritance hierarchy would be very difficult.

--
jian
https://www.enterprisedb.com/