Fix ALTER COLUMN ... DROP EXPRESSSION with subpartitions
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t139236psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 03, 2026 at 04:36 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t139236_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t139236_1 && git checkout t139236_1Patchset v1 (message #1) is on t139236_1
While working on [0]/messages/by-id/abkrpUwlGngF4e-d@phidippus.sen.work, 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]See 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated discussion at /messages/by-id/2793383.1672944799@sss.pgh.pa.us.
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]: /messages/by-id/abkrpUwlGngF4e-d@phidippus.sen.work
[1]: See 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated discussion at /messages/by-id/2793383.1672944799@sss.pgh.pa.us
discussion at /messages/by-id/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