Thread

1. Logical replication fails when partition column order differs from parent

   Вадим Ковтун <kovtunvadim@gmail.com> — 2025-10-13T12:07:03Z

   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);
   
   
      2.
   
      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);
   
   
      3.
   
      Attach the existing table as a partition:
   
   ALTER TABLE payment.payment_orders_test
   ATTACH PARTITION payment.payment_orders_partition_testFOR VALUES FROM
   ('2025-01-01') TO ('2025-12-31');
   
   
      4.
   
      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_typeFROM parent pFULL JOIN part c ON
   p.ordinal_position = c.ordinal_positionWHERE
   COALESCE(p.column_name,'') <> COALESCE(c.column_name,'')ORDER BY
   parent_pos;
   

2. RE: Logical replication fails when partition column order differs from parent

   Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2025-10-14T06:53:00Z

   Dear Вадим,
   
   Thanks for reporting the issue. I want you to provide some more information.
   
   > Steps to Reproduce (PostgreSQL logical replication):
   > ...
   
   Please clarify on which node we must run SQLs. Also, when should we create
   publication and subscriptions?
   
   To understand more clearly, can you provide a complete script to reproduce the
   failure? It should be started from the initdb command.
   
   Best regards,
   Hayato Kuroda
   FUJITSU LIMITED