The following bug has been logged on the website:
Bug reference: 19100
Logged by: haiyang li
Email address: mohen.lhy@alibaba-inc.com
PostgreSQL version: 18.0
Operating system: any
Description:
Hello all,
Recently I ran into an issue with partitioned tables where a detach
pending child table having a different column type from its parent
led to unexpected errors. Here is how to reproduce:
```
-- s1
CREATE TABLE users (id int, name text) PARTITION BY HASH (id);
CREATE TABLE users_p0 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE INDEX ON users (id);
--s2
begin;
select * from users;
-- s1
ALTER TABLE users detach partition users_p0 concurrently ;
-- cancel request to archive detach pending status
-- s2
rollback;
-- s1
ALTER TABLE users ALTER COLUMN name TYPE char(10);
\d+ users
-- error
\d+ users_p0
ERROR: could not convert row type
DETAIL: Attribute "name" of type users does not match corresponding
attribute of type users_p0.
\q
pg_dump -U xxx -d xxx -s -t users* > users.sql
-- error
create database test;
\c test
-- error
\i users.sql
...
psql:users.sql:89: ERROR: child table "users_p0" has different type for
column "name"
...
psql:users.sql:152: ERROR: cannot attach index "users_p0_id_idx" as a
partition of index "users_id_idx"
DETAIL: Index "users_p0_id_idx" is not an index on any partition of table
"users".
...
```
It's evident that in both error cases, the failure occurs because
column name has different data types between the parent and the child
table. BTW, "\d+ users_p0" error happens when
pg_get_partition_constraintdef() is called. The root cause seems to be
that when running:
```
ALTER TABLE users ALTER COLUMN name TYPE char(10);
```
on the parent table, the statement does not affect a child table that is in
detach pending state.
IMO, a detach pending child table is not affected by parent's DML or DQL,
but should still be affected by parent's DDL. My reasoning is:
1. A detach pending child table has not actually been detached yet; only
pg_inherits.inhdetachpending is marked as true. In other respects, it is
still the same as any other child partition. If DDL changes are not
propagated,
running the same command later on the detach pending child may produce
unexpected errors (e.g. when calling pg_get_partition_constraintdef()).
2. Other partition objects on a detach pending child (e.g. indexes) are not
marked as "detach pending" themselves. If you DROP INDEX on the parent, the
drop
is still propagated and the index on the child is removed. Dropping the
parent
table will also drop a detach pending child table. Thus, some DDL from the
parent
is still applied to detach pending children, creating a confusing partial
state.
Looking Forward to your feedback.
Regards,
Haiyang Li
