Logical replication fails when partition column order differs from parent
От | Вадим Ковтун |
---|---|
Тема | Logical replication fails when partition column order differs from parent |
Дата | |
Msg-id | CAHb3yJOPoSsXV6UCpjT5rRDUeJvDNBX+U3hsKdetBkC9v8Q26A@mail.gmail.com обсуждение исходный текст |
Ответы |
RE: Logical replication fails when partition column order differs from parent
|
Список | pgsql-bugs |
publish_via_partition_root=true
Description:
PostgreSQL allows partitions to have a different column ordinal_position
than their parent table. PostgreSQL itself handles this correctly internally. However, when using logical replication (e.g., pgoutput
), some replication clients assume partitions share the same column order as the parent. This can lead to incorrect value-to-column mapping and runtime errors on the subscriber side.
Does it reproduce on the most recent release?
Yes, PostgreSQL 18
Steps to Reproduce (PostgreSQL logical replication):
Create a table that will become a partition (columns ordered ip_state
before http_code
):
CREATE TABLE payment.payment_orders_partition_test ( id BIGSERIAL, at DATE NOT NULL, ip_state VARCHAR(10), http_code INT, PRIMARY KEY (id, at)
);
INSERT INTO payment.payment_orders_partition_test (id, at, ip_state, http_code)
VALUES (1, '2025-01-01', 'AI', 5);
Create a parent partitioned table with a different column order (http_code
before ip_state
):
CREATE TABLE payment.payment_orders_test ( id BIGSERIAL, at DATE NOT NULL, http_code INT, ip_state VARCHAR(10), PRIMARY KEY (id, at)
) PARTITION BY RANGE (at);
Attach the existing table as a partition:
ALTER TABLE payment.payment_orders_test
ATTACH PARTITION payment.payment_orders_partition_test
FOR VALUES FROM ('2025-01-01') TO ('2025-12-31');
Insert another row into the partition:
INSERT INTO payment.payment_orders_partition_test (id, at, ip_state, http_code)
VALUES (6, '2025-01-01', 'IA', 5);
Diagnostic Query (compare parent vs partition column order):
WITH parent AS ( SELECT ordinal_position, column_name, data_type FROM information_schema.columns WHERE table_schema = 'payment' AND table_name = 'payment_orders_test'
),
part AS ( SELECT ordinal_position, column_name, data_type FROM information_schema.columns WHERE table_schema = 'payment' AND table_name = 'payment_orders_partition_test'
)
SELECT p.ordinal_position AS parent_pos, p.column_name AS parent_col, p.data_type AS parent_type, c.ordinal_position AS part_pos, c.column_name AS part_col, c.data_type AS part_type
FROM parent p
FULL JOIN part c ON p.ordinal_position = c.ordinal_position
WHERE COALESCE(p.column_name,'') <> COALESCE(c.column_name,'')
ORDER BY parent_pos;
В списке pgsql-bugs по дате отправления: