Re: Proposal: Conflict log history table for Logical Replication

On Tue, Dec 2, 2025 at 12:38 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Tue, Dec 2, 2025 at 12:06 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> >
> > Okay, I will try to make multiple local rows as JSON Array in the next version.
> >
> Just to clarify so that we are on the same page, along with the local
> tuple the other local fields like local_xid, local_commit_ts,
> local_origin will also be converted into the array. Hope that makes
> sense?
>

Yes, what about key_tuple or RI?

> So we will change the table like this, not sure if this makes sense to
> keep all local array fields nearby in the table, or let it be near the
> respective remote field, like we are doing now remote_xid and local
> xid together etc.
>

It is better to keep the array fields together at the end. I think it
would be better to read via CLI. Also, it may take more space due to
padding/alignment if we store fixed-width and variable-width columns
interleaved and similarly the access will also be slower for
interleaved cases.

Having said that, can we consider an alternative way to store all
local_conflict_info together as a JSONB column (that can be used to
store an array of objects). For example, the multiple conflicting
tuple information can be stored as:

[
{ "xid": "1001", "commit_ts": "2023-10-27 10:00:00", "origin":
"node_A", "tuple": { "id": 1, "email": "a(at)b(dot)com" } },
{ "xid": "1005", "commit_ts": "2023-10-27 10:01:00", "origin":
"node_B", "tuple": { "id": 2, "phone": "555-0199" } }
]

To access JSON array columns, I think one needs to use the unnest
function, whereas JSONB could be accessed with something like: "SELECT
* FROM conflicts WHERE local_conflicts @> '[{"xid": "1001"}]".

--
With Regards,
Amit Kapila.