On Mon, Dec 8, 2025 at 3:01 PM Dilip Kumar <[email protected]> wrote: > > On Mon, Dec 8, 2025 at 2:38 PM Amit Kapila <[email protected]> wrote: > > > > On Mon, Dec 8, 2025 at 10:25 AM Dilip Kumar <[email protected]> wrote: > > > > > > On Thu, Dec 4, 2025 at 4:20 PM Amit Kapila <[email protected]> > > > wrote: > > > > > > > > On Thu, Dec 4, 2025 at 10:49 AM Dilip Kumar <[email protected]> > > > > 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? -- With Regards, Amit Kapila.
