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

On Thu, Aug 14, 2025 at 4:26 PM Alastair Turner <minion@decodable.me> wrote:
>
> On Wed, 13 Aug 2025 at 11:09, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>
>> On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>> >
>> > On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com> wrote:
>> > >
>> > > On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>> > > >
>> > > > So logically for PostgreSQL its an
>> > > > user table but yeah this is created and managed by the extension.
>> > > >
>> > >
>> > > Any idea if the user can alter/drop or perform any DML on it? I could
>> > > not find any details on this part.
>> >
>> > In my experience, for such extension managed tables where we want them
>> > to behave like catalog, generally users are just granted with SELECT
>> > permission.  So although it is not a catalog but for accessibility
>> > wise for non admin users it is like a catalog.  IMHO, even if we
>> > choose to create a user table for conflict log history we can also
>> > control the permissions similarly.  What's your opinion on this?
>> >
>>
>> Yes, I think it is important to control permissions on this table even
>> if it is a user table. How about giving SELECT, DELETE, TRUNCATE
>> permissions to subscription owner assuming we create one such table
>> per subscription?
>>
>> It should be a user table due to following reasons (a) It is an ever
>> growing table by definition and we need some level of user control to
>> manage it (like remove the old data); (b) We may want some sort of
>> partitioning streategy to manage it, even though, we decide to do it
>> ourselves now but in future, we should allow user to also specify it;
>> (c) We may also want user to specify what exact information she wants
>> to get stored considering in future we want resolutions to also be
>> stored in it. See a somewhat similar proposal to store errors during
>> copy by Tom [1]; (d) In a near-by thread, we are discussing storing
>> errors during copy in user table [2] and we have some similarity with
>> that proposal as well.
>>
>> If we agree on this then the next thing to consider is whether we
>> allow users to create such a table or do it ourselves. In the long
>> term, we may want both but for simplicity, we can auto-create
>> ourselves during CREATE SUBSCRIPTION with some option. BTW, if we
>> decide to let user create it then we can consider the idea of TYPED
>> tables as discussed in emails [3][4].
>
>
> Having it be a user table, and specifying the table per subscription sounds good. This is very similar to how the
loaderror tables for CloudBerry behave, for instance. To have both options for table creation, CREATE ... IF NOT EXISTS
semanticswork well - if the option on CREATE SUBSCRIPTION specifies an existing table of the right type use it, or
createone with the name supplied. This would also give the user control over whether to have one table per
subscription,one central table or anything in between. 
>

Sounds reasonable. I think the first version we can let such a table
be created automatically with some option(s) with subscription. Then,
in subsequent versions, we can extend the functionality to allow
existing tables.

>
> Rather than constraining permissions on the table, the CREATE SUBSCRIPTION command could create a dependency
relationshipbetween the table and the subscription.This would prevent removal of the table, even by a superuser. 
>

Okay, that makes sense. But, we still probably want to disallow users
from inserting or updating rows in the conflict table.

>>
>> For user tables, we need to consider how to avoid replicating these
>> tables for publications that use FOR ALL TABLES specifier. One idea is
>> to use EXCLUDE table functionality as being discussed in thread [5]
>> but that would also be a bit tricky especially if we decide to create
>> such a table automatically. One naive idea is that internally we skip
>> sending changes from this table for "FOR ALL TABLES" publication, and
>> we shouldn't allow creating publication for this table. OTOH, if we
>> allow the user to create and specify this table, we can ask her to
>> specify with EXCLUDE syntax in publication. This needs more thoughts.
>
>
> If a dependency relationship is established between the error table and the subscription, could this be used as a
basisfor filtering the error tables from FOR ALL TABLES subscriptions? 
>

Yeah, that is worth considering.

--
With Regards,
Amit Kapila.