Horton

Thread

  1. BUG #19100: Different column type between partitioned table and detached pending partition table make errors

    PG Bug reporting form <noreply@postgresql.org> — 2025-11-01T18:36:26Z

    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

  2. Re: BUG #19100: Different column type between partitioned table and detached pending partition table make errors

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-02T18:36:12Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

Ugh.

> IMO, a detach pending child table is not affected by parent's DML or DQL,
> but should still be affected by parent's DDL.

I think it'd be far safer to refuse all forms of ALTER TABLE until the
detach is completed.

			regards, tom lane

  3. Re: BUG #19100: Different column type between partitioned table and detached pending partition table make errors

    Haiyang Li <mohen.lhy@alibaba-inc.com> — 2025-11-03T09:16:02Z

    Hello,
Thanks for your opinion.
Tom Lane <tgl@sss.pgh.pa.us> 2025-11-3日 02:36 write:
>> IMO, a detach pending child table is not affected by parent's DML or DQL,
>> but should still be affected by parent's DDL.
>
>I think it'd be far safer to refuse all forms of ALTER TABLE until the
> detach is completed.
I have been trying to think of a concrete scenario where this could cause problems
if ALTER TABLE permitted on detach pending partitions.
But I haven't come up with one yet. Could you provide an example or suggest some
possible cases?
OTOH, from a safety perspective, I would argue that pg_dump should avoid emitting
ATTACH PARTITION statements for detach pending child tables.
Regards,
Haiyang Li
------------------------------------------------------------------
From:Tom Lane <tgl@sss.pgh.pa.us>
Send Time:2025年11月3日(周一) 02:36
To:"李海洋(陌痕)"<mohen.lhy@alibaba-inc.com>
CC:"pgsql-bugs"<pgsql-bugs@lists.postgresql.org>
Subject:Re: BUG #19100: Different column type between partitioned table and detached pending partition table make errors
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
Ugh.
> IMO, a detach pending child table is not affected by parent's DML or DQL,
> but should still be affected by parent's DDL.
I think it'd be far safer to refuse all forms of ALTER TABLE until the
detach is completed.
 regards, tom lane

  4. Re: BUG #19100: Different column type between partitioned table and detached pending partition table make errors

    Haiyang Li <mohen.lhy@alibaba-inc.com> — 2025-11-05T14:59:39Z

    From: Haiyang Li <mohen.lhy@alibaba-inc.com> 2025-11-3 17:16 write:
> OTOH, from a safety perspective, I would argue that pg_dump should avoid
> emitting ATTACH PARTITION statements for detach pending child tables.
I mean that if detach pending state is considered closer to the detached state,
then it would be more reasonable for pg_dump to avoid generating ATTACH
statements for detach pending tables. This makes more sense to me.
Regards,
Haiyang Li