Re: Proposal: Conflict log history table for Logical Replication - Mailing list pgsql-hackers

On Mon, Dec 8, 2025 at 3:21 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Dec 8, 2025 at 3:01 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Mon, Dec 8, 2025 at 2:38 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > On Mon, Dec 8, 2025 at 10:25 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > > >
> > > > On Thu, Dec 4, 2025 at 4:20 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > > >
> > > > > On Thu, Dec 4, 2025 at 10:49 AM Dilip Kumar <dilipbalaut@gmail.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.

--
Regards,
Dilip Kumar
Google