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

On Wed, Aug 13, 2025 at 3:39 PM 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?

Right, we need to control the permission.  I am not sure whether we
want a per subscription table or a common one. Earlier I was thinking
of a single table, but I think per subscription is not a bad idea
especially for managing the permissions.  And there can not be a
really huge number of subscriptions that we need to worry about
creating many conflict log history tables and that too we will only
create such tables when users pass that subscription option.


> 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;

Maybe we can partition by range on date (when entry is inserted) .
That way it would be easy to get rid of older partitions for users.

> (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.

Right, we may consider that 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].

Yeah that's an interesting option.

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

Yes this needs more thought, I will think more on this point and respond.

Yet another question is about table names, whether we keep some
standard name like conflict_log_history_$subid or let users pass the
name.

--
Regards,
Dilip Kumar
Google