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


This issue occurs regardless of the 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):

  1. 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);
  1. 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);
  1. 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');
  1. 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 по дате отправления: