Re: Proposal: Conflict log history table for Logical Replication

On Tue, Dec 9, 2025 at 10:12 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Dec 8, 2025 at 5:15 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Mon, Dec 8, 2025 at 3:21 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > On Mon, Dec 8, 2025 at 3:01 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > On Mon, Dec 8, 2025 at 2:38 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Mon, Dec 8, 2025 at 10:25 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > On Thu, Dec 4, 2025 at 4:20 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > > > >
> > > > > > > On Thu, Dec 4, 2025 at 10:49 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > > > > > >
> > > > > > > > > ---
> > > > > > > > > I think the conflict history table should not be transferred to the
> > > > > > > > > new cluster when pg_upgrade since the table definition could be
> > > > > > > > > different across major versions.
> > > > > > > >
> > > > > > > > Let me think more on this with respect to behaviour of other factors
> > > > > > > > like subscriptions etc.
> > > > > > > >
> > > > > > >
> > > > > > > Can we deal with different schema of tables across versions via
> > > > > > > pg_dump/restore during upgrade?
> > > > > > >
> > > > > >
> > > > > > While handling the case of conflict_log_table option during pg_dump, I
> > > > > > realized that the restore is trying to create conflict log table 2
> > > > > > different places 1) As part of the regular table dump 2) As part of
> > > > > > the CREATE SUBSCRIPTION when conflict_log_table option is set.
> > > > > >
> > > > > > So one option is we can avoid dumping the conflict log tables as part
> > > > > > of the regular table dump if we think that we do not need to conflict
> > > > > > log table data and let it get created as part of the create
> > > > > > subscription command, OTOH if we think we want to keep the conflict
> > > > > > log table data,
> > > > > >
> > > > >
> > > > > We want to retain conflict_history after upgrade. This is required for
> > > > > various reasons (a) after upgrade DBA user will still require to
> > > > > resolved the pending unresolved conflicts, (b) Regulations often
> > > > > require keeping audit trails for a longer period of time. If a
> > > > > conflict occurred at time X (which is less than the regulations
> > > > > requirement) regarding a financial transaction, that record must
> > > > > survive the upgrade, (c)
> > > > > If something breaks after the upgrade (e.g., missing rows, constraint
> > > > > violations), conflict history helps trace root causes. It shows
> > > > > whether issues existed before the upgrade or were introduced during
> > > > > migration, (d) as users can query the conflict_history tables, it
> > > > > should be treated similar to user tables.
> > > > >
> > > > > BTW, we are also planning to migrate commit_ts data in thread [1]
> > > > > which would be helpful for conflict_resolutions after upgrade.
> > > > >
> > > > > let it get dumped as part of the regular tables and in
> > > > > > CREATE SUBSCRIPTION we will just set the option but do not create the
> > > > > > table,
> > > > > >
> > > > >
> > > > > Yeah, we can turn this option during CREATE SUBSCRIPTION so that it
> > > > > doesn't try to create the table again.
> > > > >
> > > > > > although we might need to do special handling of this case
> > > > > > because if we allow the existing tables to be set as conflict log
> > > > > > tables then it may allow other user tables to be set, so need to think
> > > > > > how to handle this if we need to go with this option.
> > > > > >
> > > > >
> > > > > Yeah, probably but it should be allowed internally only not to users.
> > > >
> > > > Yeah I wanted to do that, but problem is with dump and restore, I mean
> > > > if you just dump into a sql file and execute the sql file at that time
> > > > the CREATE SUBSCRIPTION with conflict_log_table option will fail as
> > > > the table already exists because it was restored as part of the dump.
> > > > I know under binary upgrade we have binary_upgrade flag so can do
> > > > special handling not sure how to distinguish the sql executing as part
> > > > of the restore or normal sql execution by user?
> > > >
> > >
> > > See dumpSubscription(). We always use (connect = false) while dumping
> > > subscription, so, similarly, we should always dump the new option with
> > > default value which not to create the history table. Won't that be
> > > sufficient?
> >
> > Thinking out loud, so basically what we need is we need to create
> > subscription and set the conflict log table in catalog entry of the
> > subscription in pg_subscription but do not want to create the conflict
> > log table, so seems like we need to invent something new which set the
> > conflict log table in catalog but do not create the table. Currently
> > we have a single option that if conflict_log_table='table_name' is set
> > then we will create the table as well as set the table name in the
> > catalog, so need to think of something on the line of separating this,
> > or something more innovative.
> >
>
> This needs more thought and discussion, so it is better to separate
> out this part at this stage and let's try to review the core patch
> first.

+1

BTW, I told a few days back to have two options (instead of a
> single option conflict_log_table) to allow extension of more ways to
> LOG the conflict data.

Yeah, I will put that as well in an add on patch, once I fix all the
option issues of the core patch.

--
Regards,
Dilip Kumar
Google