Fix ALTER COLUMN ... DROP EXPRESSSION with subpartitions

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

Hello Alberto,

On 2026-Apr-07, Alberto Piai wrote:

> 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.

Yep, confirmed.

> PFA a fix for this and a test case.
>
> I added the test case to generated_stored.sql,

Looks good. I pushed your fix, with two minor changes:
1. acquiring a lock in the find_inheritance_children() call is
confusing and unnecessary, because ATSimpleRecursion already did it,
so I removed that by passing NoLock.

2. I removed the comment that suggested that the functionality could be
implemented with some effort. This was foreclosed by 8bf6ec3ba3a4,
so the comment is false and wrong.

I also moved the test to the exact spot where ALTER TABLE DROP
EXPRESSION is being tested. That gave me the perfect placement for the
corresponding test for the legacy-inheritance part of the functionality.

The backpatch was pretty straightforward (mostly because git-cherry-pick
figured out by itself that it needed to apply the generated_stored.sql
patch to generated.sql at the point where it was renamed.)

I think you didn't add a commitfest entry for this. Please don't forget
to create one for every patch that you submit; otherwise they're likely
to fall through the cracks. (Though these days the CF process seems
more and more to be a mostly useless, abandoned chore.)

Thanks!

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
<Schwern> It does it in a really, really complicated way
<crab> why does it need to be complicated?
<Schwern> Because it's MakeMaker.

On Tue Aug 4, 2026 at 9:26 AM CEST, Álvaro Herrera wrote:
> Hello Alberto,
>
> On 2026-Apr-07, Alberto Piai wrote:
>
>> 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.
>
> Yep, confirmed.
>
>> PFA a fix for this and a test case.
>>
>> I added the test case to generated_stored.sql,
>
> Looks good. I pushed your fix, with two minor changes:

Thanks for pushing this, and for taking the time to explain your
improvements. Much appreciated!

>
> I think you didn't add a commitfest entry for this.

I created one a while back, for some reason it didn't pick up your
latest mail. But that's maybe because it's assigned to PG20-1 which is
closed now. Anyway I updated it, so that's wrapped up now :)

Alberto

--
Alberto Piai
Sensational AG
Zürich, Switzerland