Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix RI fast-path for domain-typed FK columns

  2. pg_walsummary: Improve stability of test checking statistics

  1. [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Mikhail Kharitonov <mikhail.kharitonov.dev@gmail.com> — 2025-05-05T08:18:18Z

    Hi hackers,

An inconsistency was observed when using logical replication on partitioned
tables with the option `publish_via_partition_root = true`: if REPLICA IDENTITY
FULL is set only on the parent table, but not on all partitions, logical
decoding emits UPDATE and DELETE messages with tag 'O' (old tuple) even for
partitions that do not have full replica identity. In those cases, only the
primary key columns are included in the message, which contradicts the expected
meaning of 'O' and violates the logical replication message protocol:

https://www.postgresql.org/docs/current/protocol-logicalrep-message-formats.html

This can cause issues in downstream consumers, which interpret
the 'O' tag as implying that a full tuple is present.

The attached patch resolves the inconsistency by selecting the correct tuple
type ('O' vs 'K') based on the replica identity of the actual leaf relation
being published, rather than using the setting of the root relation alone.
As a result, the format of logical replication messages aligns with
the semantics
defined by the protocol.

Steps to reproduce:

  1. Create a partitioned table with REPLICA IDENTITY FULL on the parent
     and only one of the partitions.

  2. Create a publication with `publish_via_partition_root = true`.

  3. Perform INSERT, UPDATE, DELETE operations through the root table.

  4. Observe via `pg_recvlogical` that for a partition without full replica
     identity, the logical replication stream contains 'O' records with
     only key fields.

After applying the patch, 'O' is used only when the full row is available,
and 'K' is used otherwise - as expected.

This patch is based on the current `master` branch as of commit: b3754dcc9ff

Best regards,
Mikhail Kharitonov

  2. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Maxim Orlov <orlovmg@gmail.com> — 2025-05-12T14:25:00Z

    Hi!

This is probably not the most familiar part of Postgres to me, but does it
break anything? Or is it just inconsistency in the replication protocol?

A test for the described scenario would be a great addition. And, if it is
feasible, provide an example of what would be broken with the way
partitioned tables are replicated now.

There is a chance that the replication protocol for partitioned tables
needs to be rewritten, and I sincerely hope that I am wrong about this. It
seems Alvaro Herrera tried this here [0].


[0]
https://www.postgresql.org/message-id/201902041630.gpadougzab7v@alvherre.pgsql

-- 
Best regards,
Maxim Orlov.

  3. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Mikhail Kharitonov <mikhail.kharitonov.dev@gmail.com> — 2025-05-29T06:30:31Z

    Hi,

Thank you for the feedback.

I would like to clarify that the current behavior does not break replication
between PostgreSQL instances. The logical replication stream is still accepted
by the subscriber, and the data is applied correctly. However, the protocol
semantics are violated, which may cause issues for external systems that rely
on interpreting this stream.

When using publish_via_partition_root = true and setting REPLICA IDENTITY FULL
only on the parent table (but not on all partitions), logical replication
generates messages with the tag 'O' (old tuple) for updates and deletes even
for partitions that do not have full identity configured.

In those cases, only key columns are sent, and the rest of the tuple is omitted.
 This contradicts the meaning of tag 'O', which, according
 to the documentation [1], indicates that the full old tuple is included.

This behavior is safe for the standard PostgreSQL subscriber, which does not
rely on the tag when applying changes. However, third-party tools that consume
the logical replication stream and follow the protocol strictly can be misled.
For example, one of our clients uses a custom CDC mechanism that extracts
changes and sends them to Oracle. Their handler interprets the 'O' tag as a
signal that the full old row is available. When it is not - the data is
processed incorrectly.

The attached patch changes the behavior so that the 'O' or 'K' tag is chosen
based on the REPLICA IDENTITY setting of the actual partition where the row
ends up not only the parent.
    - If the partition has REPLICA IDENTITY FULL, the full tuple is
sent and tagged 'O'.
    - Otherwise, only the key columns are sent, and the tag 'K' is used.

This aligns the behavior with the protocol documentation.
I have also included a TAP test: 036_partition_replica_identity.pl,
located in src/test/subscription/t/

It demonstrates two cases:
    - An update/delete on a partition with REPLICA IDENTITY FULL correctly
    emits an 'O' tag with the full old row.
    - An update/delete on a partition without REPLICA IDENTITY FULL currently
    also emits an 'O' tag, but only with key fields - this is the problem.

After applying the patch, the second case correctly uses the 'K' tag.

This patch is a minimal change it does not alter protocol structure
or introduce new behavior. It only ensures the implementation matches
the documentation. In the future, we might consider a broader redesign
of logical replication for partitioned tables (see [2]), but this is
a narrow fix that solves a real inconsistency.

Looking forward to your comments.

Best regards,
Mikhail Kharitonov

[1] https://www.postgresql.org/docs/current/protocol-logicalrep-message-formats.html
[2] https://www.postgresql.org/message-id/201902041630.gpadougzab7v@alvherre.pgsql

On Mon, May 12, 2025 at 5:25 PM Maxim Orlov <orlovmg@gmail.com> wrote:
>
> Hi!
>
> This is probably not the most familiar part of Postgres to me, but does it break anything? Or is it just inconsistency in the replication protocol?
>
> A test for the described scenario would be a great addition. And, if it is feasible, provide an example of what would be broken with the way partitioned tables are replicated now.
>
> There is a chance that the replication protocol for partitioned tables needs to be rewritten, and I sincerely hope that I am wrong about this. It seems Alvaro Herrera tried this here [0].
>
>
> [0] https://www.postgresql.org/message-id/201902041630.gpadougzab7v@alvherre.pgsql
>
>
> --
> Best regards,
> Maxim Orlov.

  4. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Mikhail Kharitonov <mikhail.kharitonov.dev@gmail.com> — 2025-07-08T08:53:43Z

    Hi all,

I’m sending v2 of the patch. This is a clean rebase onto current master
(commit a27893df45e) and a squash of the fix together with the TAP
test into a single patch file.

I would appreciate your thoughts and comments on the current problem.

Thank you!

--
Best regards,
Mikhail Kharitonov

On Thu, May 29, 2025 at 9:30 AM Mikhail Kharitonov
<mikhail.kharitonov.dev@gmail.com> wrote:
>
> Hi,
>
> Thank you for the feedback.
>
> I would like to clarify that the current behavior does not break replication
> between PostgreSQL instances. The logical replication stream is still accepted
> by the subscriber, and the data is applied correctly. However, the protocol
> semantics are violated, which may cause issues for external systems that rely
> on interpreting this stream.
>
> When using publish_via_partition_root = true and setting REPLICA IDENTITY FULL
> only on the parent table (but not on all partitions), logical replication
> generates messages with the tag 'O' (old tuple) for updates and deletes even
> for partitions that do not have full identity configured.
>
> In those cases, only key columns are sent, and the rest of the tuple is omitted.
>  This contradicts the meaning of tag 'O', which, according
>  to the documentation [1], indicates that the full old tuple is included.
>
> This behavior is safe for the standard PostgreSQL subscriber, which does not
> rely on the tag when applying changes. However, third-party tools that consume
> the logical replication stream and follow the protocol strictly can be misled.
> For example, one of our clients uses a custom CDC mechanism that extracts
> changes and sends them to Oracle. Their handler interprets the 'O' tag as a
> signal that the full old row is available. When it is not - the data is
> processed incorrectly.
>
> The attached patch changes the behavior so that the 'O' or 'K' tag is chosen
> based on the REPLICA IDENTITY setting of the actual partition where the row
> ends up not only the parent.
>     - If the partition has REPLICA IDENTITY FULL, the full tuple is
> sent and tagged 'O'.
>     - Otherwise, only the key columns are sent, and the tag 'K' is used.
>
> This aligns the behavior with the protocol documentation.
> I have also included a TAP test: 036_partition_replica_identity.pl,
> located in src/test/subscription/t/
>
> It demonstrates two cases:
>     - An update/delete on a partition with REPLICA IDENTITY FULL correctly
>     emits an 'O' tag with the full old row.
>     - An update/delete on a partition without REPLICA IDENTITY FULL currently
>     also emits an 'O' tag, but only with key fields - this is the problem.
>
> After applying the patch, the second case correctly uses the 'K' tag.
>
> This patch is a minimal change it does not alter protocol structure
> or introduce new behavior. It only ensures the implementation matches
> the documentation. In the future, we might consider a broader redesign
> of logical replication for partitioned tables (see [2]), but this is
> a narrow fix that solves a real inconsistency.
>
> Looking forward to your comments.
>
> Best regards,
> Mikhail Kharitonov
>
> [1] https://www.postgresql.org/docs/current/protocol-logicalrep-message-formats.html
> [2] https://www.postgresql.org/message-id/201902041630.gpadougzab7v@alvherre.pgsql
>
> On Mon, May 12, 2025 at 5:25 PM Maxim Orlov <orlovmg@gmail.com> wrote:
> >
> > Hi!
> >
> > This is probably not the most familiar part of Postgres to me, but does it break anything? Or is it just inconsistency in the replication protocol?
> >
> > A test for the described scenario would be a great addition. And, if it is feasible, provide an example of what would be broken with the way partitioned tables are replicated now.
> >
> > There is a chance that the replication protocol for partitioned tables needs to be rewritten, and I sincerely hope that I am wrong about this. It seems Alvaro Herrera tried this here [0].
> >
> >
> > [0] https://www.postgresql.org/message-id/201902041630.gpadougzab7v@alvherre.pgsql
> >
> >
> > --
> > Best regards,
> > Maxim Orlov.

  5. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Mikhail Kharitonov <mikhail.kharitonov.dev@gmail.com> — 2025-07-08T09:41:40Z

    On Tue, Jul 8, 2025 at 11:53 AM Mikhail Kharitonov
<mikhail.kharitonov.dev@gmail.com> wrote:
>
> Hi all,
>
> I’m sending v2 of the patch. This is a clean rebase onto current master
> (commit a27893df45e) and a squash of the fix together with the TAP
> test into a single patch file.
>
> I would appreciate your thoughts and comments on the current problem.
>
> Thank you!
>
> --
> Best regards,
> Mikhail Kharitonov
>

Sorry, I forgot the attachment in my previous message.
Please find the v2 patch attached.

  6. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Mikhail Kharitonov <mikhail.kharitonov.dev@gmail.com> — 2025-08-12T12:02:33Z

    Hi all,

I've rebased this series onto the latest master.

Changes in v3:

    Patch 1/2 adds two new functions: logicalrep_write_update_extended,
    logicalrep_write_delete_extended to logicalproto.
    These are now used in pgoutput and allow correct old-tuple flag handling
    when publish_via_partition_root = true.
    The old functions remain as wrappers to preserve compatibility.
    A short documentation note was added to explain the new behaviour.

    Patch 2/2 moves the TAP test into a separate commit,
    so the code change and test are isolated.

--
Best regards,
Mikhail Kharitonov

  7. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Masahiko Sawada <sawada.mshk@gmail.com> — 2026-01-07T02:14:44Z

    On Mon, May 5, 2025 at 1:56 AM Mikhail Kharitonov
<mikhail.kharitonov.dev@gmail.com> wrote:
>
> Hi hackers,
>
> An inconsistency was observed when using logical replication on partitioned
> tables with the option `publish_via_partition_root = true`: if REPLICA IDENTITY
> FULL is set only on the parent table, but not on all partitions, logical
> decoding emits UPDATE and DELETE messages with tag 'O' (old tuple) even for
> partitions that do not have full replica identity. In those cases, only the
> primary key columns are included in the message, which contradicts the expected
> meaning of 'O' and violates the logical replication message protocol:
>
> https://www.postgresql.org/docs/current/protocol-logicalrep-message-formats.html
>
> This can cause issues in downstream consumers, which interpret
> the 'O' tag as implying that a full tuple is present.
>
> The attached patch resolves the inconsistency by selecting the correct tuple
> type ('O' vs 'K') based on the replica identity of the actual leaf relation
> being published, rather than using the setting of the root relation alone.
> As a result, the format of logical replication messages aligns with
> the semantics
> defined by the protocol.
>
> Steps to reproduce:
>
>   1. Create a partitioned table with REPLICA IDENTITY FULL on the parent
>      and only one of the partitions.
>
>   2. Create a publication with `publish_via_partition_root = true`.
>
>   3. Perform INSERT, UPDATE, DELETE operations through the root table.
>
>   4. Observe via `pg_recvlogical` that for a partition without full replica
>      identity, the logical replication stream contains 'O' records with
>      only key fields.

I tested this scenario but what I've seen in my env is somewhat
different from the above analysis; pgoutput plugin writes 'O' records
as you mentioned, but it doesn't omit non-key fields, but writes NULL
as non-key fields. Here are my reproducible steps:

create table p (a int not null, b int) partition by list (a);
create table c1 partition of p for values  in (1);
create table c2 partition of p for values  in (2);
create unique index on c2 (a);
alter table p replica identity full;
alter table c1 replica identity full;
alter table c2 replica identity using INDEX c2_a_idx ;
insert into p values (1, 10), (2, 20);
create publication pub for all tables with (publish_via_partition_root
= 'true');
select pg_create_logical_replication_slot('sub', 'pgoutput');
delete from p where a = 1;
delete from p where a = 2;
select encode(data, 'escape') from
pg_logical_slot_peek_binary_changes('sub', null, null,
'proto_version', '1', 'publication_names', 'pub');

The last pg_logical_slot_peek_binary_changes() writes the two 'D'
(delete) messages:

1. D\000\000@\000O\000\x02t\000\000\000\x011t\000\000\000\x0210
2. D\000\000@\000O\000\x02t\000\000\000\x012n

What we can know from these messages are:

- Both messages have 'O'.
- Both messages have two columns ('\000\x02').
- The first message has: the first column '1' (length is 1
('\000\000\000\x01')), and the second column '10' (length is 2
('\000\000\000\x02')).
- The second message has: the first column '2', and the second column
NULL ('n').

From these facts, I guess there could be problematic cases even in the
native logical replication. Here are reproducible steps:

-- Publisher
create table p (a int not null, b int) partition by list (a);
create table c1 partition of p for values  in (1);
create table c2 partition of p for values  in (2);
create unique index on c2 (a);
alter table p replica identity full;
alter table c1 replica identity full;
alter table c2 replica identity using INDEX c2_a_idx ;
insert into p values (1, 10), (2, 20);
create publication pub for all tables with (publish_via_partition_root
= 'true');

-- Subscriber
create table p (a int, b int, c int);
create subscription sub connection 'dbname=postgres port=5551' publication pub;

-- Publisher
delete from p where a = 1; -- generate a message 'DELETE (1, 10)'
delete from p where a = 2; -- generate a message 'DELETE (2, NULL)'

The second delete message cannot find the tuple on the subscriber, so
the table contents are now inconsistent between the publisher and the
subscriber. I need more investigation to verify that it's a problem,
but this behavior doesn't change even with the proposed change.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

  8. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Alexander Korotkov <aekorotkov@gmail.com> — 2026-06-12T05:46:29Z

    Hi, Mikhail!

On Tue, Aug 12, 2025 at 3:02 PM Mikhail Kharitonov <
mikhail.kharitonov.dev@gmail.com> wrote:
> I've rebased this series onto the latest master.
>
> Changes in v3:
>
>     Patch 1/2 adds two new functions: logicalrep_write_update_extended,
>     logicalrep_write_delete_extended to logicalproto.
>     These are now used in pgoutput and allow correct old-tuple flag
handling
>     when publish_via_partition_root = true.
>     The old functions remain as wrappers to preserve compatibility.
>     A short documentation note was added to explain the new behaviour.
>
>     Patch 2/2 moves the TAP test into a separate commit,
>     so the code change and test are isolated.

Thank you for catching this.  I've some notes about your patches.

    Assert(leafrel->rd_rel->relreplident == REPLICA_IDENTITY_DEFAULT ||
           leafrel->rd_rel->relreplident == REPLICA_IDENTITY_FULL ||
           leafrel->rd_rel->relreplident == REPLICA_IDENTITY_INDEX);

This assert was in the beginning of the function.  You've moved it into the
middle of message forming.  If that's an intentional change, it must be
motivated.

# 2: second partition has REPLICA IDENTITY DEFAULT - only keys expected.
if ($wal =~ /U.*K.*second/s)
{
    pass("Tag K correctly used for partition with REPLICA IDENTITY
DEFAULT");
}
elsif ($wal =~ /(U.*O.*second)/s)
{
    my $blk = $1;
    my $count = () = $blk =~ /second/g;
    is($count, 2, "Tag O used but this partition with REPLICA IDENTITY
DEFAULT");
}

This check is very lossy.  I think it must be explicit on what count of
which records it expects.  Currently, the test silently passes if no
branches of above are taken.  Also, regexes don't look reliable, they could
easily match cross-record.  You might either write more reliable regexes,
or even better parse individual records before matching them.

Also, please note that in your test subscriber does nothing expect waiting
for sync.  If you're introducing subscriber, it worths to at least check
its final state.

------
Regards,
Alexander Korotkov
Supabase

  9. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    jihyun bahn <rring0727@gmail.com> — 2026-06-17T05:15:55Z

    The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            not tested

Hi Mikhail, and Masahiko and Alexander,

I tested v3 of this patch on current master and looked into the
subscriber-side divergence Masahiko raised in January, since it
seemed to be the open question holding the thread up.  Sharing what I
found, in case the data is useful.

Environment:
- PostgreSQL master (commit 68ace967c16)
- macOS 14 (Darwin 24.2) on Apple Silicon, Apple clang 16
- meson build with -Dcassert=true

v3-0001 applies cleanly and builds without new warnings.

What v3 changes, on the wire
----------------------------
Using Masahiko's setup -- a list-partitioned root p with REPLICA
IDENTITY FULL, leaf c1 FULL and leaf c2 USING INDEX, published with
publish_via_partition_root = true -- I dumped the DELETE of the c2 row
with pg_logical_slot_peek_binary_changes (pgoutput, proto_version 4):

  before v3:  D ... O  t "2"  n     -- tag 'O', column b sent as NULL
  after  v3:  D ... K  t "2"  n     -- tag 'K', column b sent as NULL

So v3 correctly changes the tag from 'O' to 'K' for the leaf whose
replica identity is index-based, which is what it documents.  The
old-tuple payload itself is unchanged (b is still NULL).

What does not change, on the subscriber
---------------------------------------
The apply worker reads the 'O'/'K' tag only to decide whether an old
tuple follows; it does not branch on the tag afterwards.  So native
PostgreSQL-to-PostgreSQL apply behaves identically with and without
v3.  I confirmed this by varying only whether the subscriber table
has a key index:

  subscriber p(a, b), no PK/index:
    DELETE FROM p WHERE a = 2;   -- not applied; (2,20) remains
    LOG:    conflict detected on relation "public.p":
            conflict=delete_missing
    DETAIL: Could not find the row to be deleted:
            replica identity full (2, null).

  subscriber p(a int primary key, b):
    DELETE FROM p WHERE a = 2;   -- applied correctly, row removed

This holds both before and after v3.  So the divergence Masahiko saw
reproduces only when the subscriber lacks a replica-identity/PK index
on the key column; with such an index the key-only old tuple matches
via the index path and applies fine.  The divergence is governed by
the subscriber's index, not by the 'O'/'K' tag, which is why v3
(correctly) does not change it.

That suggests v3's effect is precisely scoped to protocol conformance
for consumers that trust the tag (external CDC), which is what it
claims.  It might be worth saying so explicitly in the commit message,
to keep it separate from the native-divergence question, which it does
not address.

On the deeper issue
-------------------
The native divergence comes from the old tuple carrying a NULL for a
column that the subscriber -- told the relation is REPLICA IDENTITY
FULL, from the root -- then compares as a real value.  I tried a small
experiment: a new per-column wire marker meaning "this column is not
part of the replica identity and was not sent", emitted under a new
protocol version for the columns outside the leaf's replica identity.
The subscriber excludes those columns from old-tuple matching, reusing
the same per-column exclusion that RelationFindDeletedTupleInfoSeq()
already does for conflict detection.  With that, the no-index
subscriber above matches the c2 DELETE by key and the divergence
disappears, while older subscribers fall back to today's behavior.  It
does not touch the Relation message.

I am not proposing that here -- it is a rough prototype and larger
than this patch's scope.  But I wanted to ask: is a direction
like that of interest as a separate patch, or is a root/leaf
replica-identity mismatch under publish_via_partition_root considered a
misconfiguration that should instead be warned about at publication
time?  I could not find the heterogeneous-RI case settled either way in
the archives -- a 2019 thread from Alvaro, "propagating replica
identity to partitions", explored making it propagate, but as far as I
can tell that did not end in a committed change (replica identity still
does not cascade to partitions today).  So I am unsure which way the
project leans.

Separately, on Alexander's point that the test only creates a
subscriber and waits for sync: I would be happy to help extend the TAP
test to assert the subscriber's final table state, if that is useful.

Regards,
Jihyun Bahn

  10. Re: [PATCH] Fix replica identity mismatch for partitioned tables with publish_via_partition_root

    Henson Choi <assam258@gmail.com> — 2026-06-27T01:11:34Z

    Hi Mikhail,

One small design question on the v3 API:

> The old functions remain as wrappers to preserve compatibility.

After the patch, though, the base functions have no callers. The only
call site (the switch in pgoutput_change()) calls the _extended forms
directly, leaving logicalrep_write_update()/logicalrep_write_delete() as
wrappers that just forward the same relation twice; a tree-wide grep
finds no caller, and no contrib output plugin uses them either.

Is there a basis for treating these as an external interface?

They look like pgoutput's internal write-side helpers, paired with the
apply worker's logicalrep_read_* side -- not a documented extension API, and
signatures here change across major versions anyway. Unless they are
commonly called from extensions, formally or informally, I don't see
what the _extended wrapping preserves.

Since there is exactly one caller, with both the leaf and the
publication relation already in scope, it seems simpler to give the base
functions the two-relation signature directly and update that one switch
-- dropping the _extended variants and the wrappers. No behavior change.

Not a blocker; happy to drop this if there's a consumer I'm missing.

Regards,
Henson