| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | 303677365(at)qq(dot)com |
| Subject: | BUG #19913: Parent ALTER TABLE during concurrent DETACH leaves pending partition unusable (42804) |
| Date: | 2026-11-17 15:01:50 |
| Message-ID: | 19913-ee9177a3015ae4be@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19913
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: Unsupported/Unknown
Operating system: Linux x86_64; master build 81b2179b492 (20devel)
Description:
**PostgreSQL version:** 20devel master build (commit 81b2179b492, built
2026-10-27). DETACH PARTITION ... CONCURRENTLY and the detach-pending state
(pg_inherits.inhdetachpending) exist since PG14, so supported versions may
be affected as well; I have not verified on a release build.
**Operating system:** Linux x86_64
**Description:**
If ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY is cancelled while
waiting for a lock, the partition is intentionally left in a "detach
pending" state (pg_inherits.inhdetachpending = true), to be finished later
with ALTER TABLE ... DETACH PARTITION ... FINALIZE. That recovery works fine
â unless, while the partition is pending, certain ALTER TABLE operations
are run on the partitioned parent. Concretely, ALTER TABLE ... ADD COLUMN on
the parent silently skips the pending partition, while that partition still
retains its partition bound. From that point on:
- INSERT and UPDATE on the partition fail with 42804 "could not convert row
type"
- pg_get_partition_constraintdef() on the partition fails with the same
error
- ALTER TABLE ... DETACH PARTITION ... FINALIZE itself fails with the same
42804, so the detach can never be completed
- a plain DETACH is rejected with 55000 ("cannot detach partition ...
already pending detach")
The only recovery I found is DROP TABLE on the partition.
A control run with the same pending state but no parent DDL finalizes
normally, so the pending state itself is recoverable; it is the parent-side
ALTER that breaks it.
**Reproduction (three sessions, READ COMMITTED):**
Setup:
```sql
CREATE TABLE g (val int, data int) PARTITION BY LIST (val);
CREATE TABLE p1 PARTITION OF g FOR VALUES IN (1);
CREATE TABLE p2 PARTITION OF g FOR VALUES IN (2);
INSERT INTO g VALUES (1, 111), (2, 222);
```
Session 1 (holds a lock, does not commit):
```sql
BEGIN;
SELECT * FROM g;
```
Session 2 (blocks waiting for session 1):
```sql
ALTER TABLE g DETACH PARTITION p1 CONCURRENTLY;
```
Session 3 (waits until the partition is pending, cancels session 2, lets
session 1 commit):
```sql
-- wait until this returns true:
SELECT inhdetachpending FROM pg_inherits WHERE inhrelid='p1'::regclass;
SELECT pg_cancel_backend(<session 2 pid>);
-- then in session 1: COMMIT;
```
The problem: a parent-side ALTER TABLE while p1 is pending, in a fresh
session:
```sql
ALTER TABLE g ADD COLUMN extra int;
```
Observed afterwards:
```text
INSERT INTO p1 VALUES (1, 999); -- ERROR 42804: could not
convert row type
UPDATE p1 SET data = 888 WHERE val = 1; -- ERROR 42804: could not
convert row type
SELECT pg_get_partition_constraintdef('p1'::regclass); -- ERROR 42804:
could not convert row type
ALTER TABLE g DETACH PARTITION p1 FINALIZE; -- ERROR 42804: could not
convert row type
ALTER TABLE g DETACH PARTITION p1; -- ERROR 55000: cannot
detach partition "p1"
DROP TABLE p1; -- OK (only recovery
found)
```
The catalog state before the DROP still shows p1 as a partition:
relispartition = true, inhdetachpending = true, relpartbound set.
Control: the same three-session construction without any parent DDL during
the pending window â INSERT, UPDATE and DETACH ... FINALIZE all succeed,
and the partition ends up properly detached.
Variants, same construction with a different parent DDL during the pending
window:
- ALTER TABLE g DROP COLUMN data; â does not break the partition;
everything including FINALIZE still works.
- ALTER TABLE g ALTER COLUMN data TYPE bigint; â behaves like ADD COLUMN:
INSERT/UPDATE/pg_get_partition_constraintdef and DETACH FINALIZE all fail
with 42804, plain DETACH 55000, only DROP TABLE recovers.
So parent row-type changes that alter the shared column layout in one
direction break the pending partition, while DROP COLUMN does not.
**Root cause (analysis):**
Parent-side ALTER TABLE recursion over the partitions skips partitions in
detach-pending state (find_inheritance_children with the omit-detached
option â presumably deliberate, to not touch a partition that is being
detached). But the pending partition still has its partition bound set and
is still treated as an attached partition by code paths that convert tuples
between the partition's row type and the parent's (now changed) row type.
Those conversions fail with "could not convert row type", and DETACH ...
FINALIZE hits the same conversion when it tries to finish the detach, which
makes the pending state unrecoverable except by dropping the partition.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Nisha Moond | 2026-11-17 15:32:59 | Re: BUG #19892: Logical replication skips UPDATE/DELETE of rows with NULL in a hash-indexed subscriber column |
| Previous Message | PG Bug reporting form | 2026-11-17 14:57:39 | BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) |