Re: Proposal: Conflict log history table for Logical Replication

On Tue, Dec 2, 2025 at 2:47 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> 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"}]".

Yeah we can do that as well, maybe that's a better idea compared to
creating separate array fields for each local element.

--
Regards,
Dilip Kumar
Google