Re: Proposal: Conflict log history table for Logical Replication

On Wed, 13 Aug 2025 at 11:09, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta(dot)malik(at)gmail(dot)com>
> wrote:
> > >
> > > On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut(at)gmail(dot)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 load error tables for CloudBerry
behave, for instance. To have both options for table creation, CREATE ...
IF NOT EXISTS semantics work well - if the option on CREATE SUBSCRIPTION
specifies an existing table of the right type use it, or create one 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.
Rather than constraining permissions on the table, the CREATE SUBSCRIPTION
command could create a dependency relationship between the table and the
subscription.This would prevent removal of the table, even by a superuser.

> 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 basis for filtering the error tables
from FOR ALL TABLES subscriptions?

Regards

Alastair