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

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. If
thesubscription 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.  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.

>>
>> 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 less
costlyoperation 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 destroyed
asa side effect of another operation. I would strongly suggest leaving the table in place when the subscription is
dropped.

Thanks for the input, I would like to hear opinions from others as
well here.  I agree that implicitly getting rid of the conflict
history might be problematic but we also need to consider that we are
considering dropping this when the whole subscription is dropped.  Not
sure even after subscription drop users will be interested in conflict
history, if yes then they need to be aware of preserving that isn't
it.

--
Regards,
Dilip Kumar
Google