BUG #19580: Can not FINALIZE detach partition. Table in stuck
The following bug has been logged on the website:
Bug reference: 19580
Logged by: Oleg Ivanov
Email address: o15611@gmail.com
PostgreSQL version: 18.4
Operating system: Linux
Description:
drop table if exists t;
CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary
key (id, reg)) PARTITION BY RANGE (id);
CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (20) PARTITION BY
RANGE(reg);
CREATE TABLE t11 PARTITION OF t1 FOR VALUES FROM (1) TO (10);
ALTER TABLE t DETACH PARTITION t1;
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (20);
"generated by default as identity" was added only to t1 and not in t11.
But, the problem is:
ALTER TABLE t DETACH PARTITION t1 CONCURRENTLY;
ERROR: column "id" of relation "t11" is not an identity column
I can not find a statement to FINALIZE detach:
ALTER TABLE t DETACH PARTITION t1;
ERROR: cannot detach partition "t1"
DETAIL: The partition is being detached concurrently or has an unfinished
detach.
HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the
pending detach operation.
ALTER TABLE t1 DETACH PARTITION t11;
ERROR: cannot alter partition "t1" with an incomplete detach
HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the
pending detach operation.
ALTER TABLE t11 ALTER COLUMN id ADD generated by default as identity;
ERROR: cannot add identity to a column of a partition
ALTER TABLE t1 ALTER COLUMN id drop identity;
ERROR: cannot alter partition "t1" with an incomplete detach
HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the
pending detach operation.
ALTER TABLE t11 ALTER COLUMN id drop identity;
ERROR: cannot drop identity from a column of a partition
ALTER TABLE t ALTER COLUMN id drop identity;
ALTER TABLE
ALTER TABLE t DETACH PARTITION t1 FINALIZE;
ERROR: column "id" of relation "t11" is not an identity column
Table in stuck.
The only way to FINALIZE is to drop section:
drop table t11;
ALTER TABLE t DETACH PARTITION t1 FINALIZE;
On 2026-Jul-25, PG Bug reporting form wrote:
drop table if exists t;
CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary
key (id, reg)) PARTITION BY RANGE (id);
CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (20) PARTITION BY
RANGE(reg);
CREATE TABLE t11 PARTITION OF t1 FOR VALUES FROM (1) TO (10);
ALTER TABLE t DETACH PARTITION t1;
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (20);"generated by default as identity" was added only to t1 and not in t11.
Hmm, I think the bug here is that ATTACH PARTITION seems to have
neglected to add the GENERATED BY expression to that column of t11.
When the table was initially created in line 4, the generation
expression was there, but DETACH removes it, and then ATTACH doesn't put
it back.
(If you start this sequence from scratch and try the DETACH CONCURRENTLY
at after creating t11 but before doing the DETACH/ATTACH dance, it seems
to work properly.)
But, the problem is:
ALTER TABLE t DETACH PARTITION t1 CONCURRENTLY;
ERROR: column "id" of relation "t11" is not an identity column
Even if the above is fixed, maybe this should somehow be made a WARNING
instead of a hard error, to avoid brittleness in DETACH in cases of
bogus catalog contents.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"XML!" Exclaimed C++. "What are you doing here? You're not a programming
language."
"Tell that to the people who use me," said XML.
https://burningbird.net/the-parable-of-the-languages/