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

On Mon, Sep 8, 2025 at 12:01 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Sun, Sep 7, 2025 at 1:42 PM Alastair Turner <minion@decodable.me> wrote:
> >
> > Hi Dilip
> >
> > Thanks for working on this, I think it will make conflict detection a lot more useful.
>
> Thanks for the suggestions, please find my reply inline.
>
> > On Sat, 6 Sept 2025, 10:38 Dilip Kumar, <dilipbalaut@gmail.com> wrote:
> >>
> >> While working on the patch, I see there are some open questions
> >>
> >> 1. We decided to pass the conflict history table name during
> >> subscription creation. And it makes sense to create this table when
> >> the CREATE SUBSCRIPTION command is executed. A potential concern is
> >> that the subscription owner will also own this table, having full
> >> control over it, including the ability to drop or alter its schema.
>
> >
> > Typed tables and the dependency framework can address this concern. The schema of a typed table cannot be changed.
Ifthe subscription is marked as a dependency of the log table, the table cannot be dropped while the subscription
exists.
>
> Yeah type table can be useful here, but only concern is when do we
> create this type.
>

How about having this as a built-in type?

>  One option is whenever we can create a catalog
> relation say "conflict_log_history" that will create a type and then
> for each subscription if we need to create the conflict history table
> we can create it as "conflict_log_history" type, but this might not be
> a best option as we are creating catalog just for using this type.
> Second option is to create a type while creating a table itself but
> then again the problem remains the same as subscription owners get
> control over altering the schema of the type itself.  So the goal is
> we want this type to be created such that it can not be altered so
> IMHO option1 is more suitable i.e. creating conflict_log_history as
> catalog and per subscription table can be created as this type.
>

I think having it as a catalog table has drawbacks like who will clean
this ever growing table. The one thing is not clear from Alastair's
response is that he said to make subscription as a dependency of
table, if we do so, then won't it be difficult to even drop
subscription and also doesn't that sound reverse of what we want.

> >>
> >> 2. A further challenge is how to exclude these tables from publishing
> >> changes. If we support a subscription-level log history table and the
> >> user publishes ALL TABLES, the output plugin uses
> >> is_publishable_relation() to check if a table is publishable. However,
> >> applying the same logic here would require checking each subscription
> >> on the node to see if the table is designated as a conflict log
> >> history table for any subscription, which could be costly.
> >
> >
> >  Checking the type of a table and/or whether a subscription object depends on it in a certain way would be a far
lesscostly operation to add to is_publishable_relation() 
> +1
>
> >
> >>
> >> 3. And one last thing is about should we consider dropping this table
> >> when we drop the subscription, I think this makes sense as we are
> >> internally creating it while creating the subscription.
> >
> >
> > Having to clean up the log table explicitly is likely to annoy users far less than having the conflict data
destroyedas a side effect of another operation. I would strongly suggest leaving the table in place when the
subscriptionis dropped. 
>
> Thanks for the input, I would like to hear opinions from others as
> well here.
>

But OTOH, there could be users who want such a table to be dropped.
One possibility is that if we user provided us a pre-created table
then we leave it to user to remove the table, otherwise, we can remove
with drop subscription. BTW, did we decide that we want a
conflict-table-per-subscription or one table for all subscriptions, if
later, then I guess the problem would be that it has to be a shared
table across databases.

--
With Regards,
Amit Kapila.