While working on [0], I noticed that DROP EXPRESSION currently refuses
to be applied to inheritance trees of depth > 2, e.g. when there are
subpartitions.
This works as expected:
CREATE TABLE gtest_root
(a int, b int, c int GENERATED ALWAYS AS (a + b) STORED)
PARTITION BY LIST (a);
CREATE TABLE gtest_leaf
PARTITION OF gtest_root FOR VALUES IN (1);
ALTER TABLE gtest_root ALTER COLUMN c DROP EXPRESSION;
while this doesn't:
CREATE TABLE gtest_root
(a int, b int, c int GENERATED ALWAYS AS (a + b) STORED)
PARTITION BY LIST (a);
CREATE TABLE gtest_node
PARTITION OF gtest_root FOR VALUES IN (1)
PARTITION BY LIST (b);
CREATE TABLE gtest_leaf
PARTITION OF gtest_node FOR VALUES IN (1);
ALTER TABLE gtest_root ALTER COLUMN c DROP EXPRESSION;
and results in
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
This seems like a simple oversight while trying to enforce that a
GENERATED column must be such in the whole inheritance tree [1].
PFA a fix for this and a test case.
I added the test case to generated_stored.sql, even though the comments
at the top say it should be kept in sync with generated_virtual.sql,
because DROP EXPRESSION is not supported for virtual generated columns.
It seemed better to keep the test case closed to the other tests of
DROP/SET EXPRESSION with partitioning, rather than putting it e.g. in
alter_table.sql, but happy to move it of course.
Kind regards,
Alberto
[0] https://postgr.es/m/abkrpUwlGngF4e-d%40phidippus.sen.work
[1] See 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated
discussion at https://postgr.es/m/2793383.1672944799@sss.pgh.pa.us
--
Alberto Piai
Sensational AG
Zürich, Switzerland
