Re: Proposal: Conflict log history table for Logical Replication

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

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.

[1] - https://www.postgresql.org/message-id/flat/752672.1699474336%40sss.pgh.pa.us#b8450be5645c4252d7d02cf7aca1fc7b
[2] - https://www.postgresql.org/message-id/CACJufxH_OJpVra%3D0c4ow8fbxHj7heMcVaTNEPa5vAurSeNA-6Q%40mail.gmail.com
[3] - https://www.postgresql.org/message-id/28c420cf-f25d-44f1-89fd-04ef0b2dd3db%40dunslane.net
[4] - https://www.postgresql.org/message-id/CADrsxdYG%2B%2BK%3DiKjRm35u03q-Nb0tQPJaqjxnA2mGt5O%3DDht7sw%40mail.gmail.com
[5] - https://www.postgresql.org/message-id/CANhcyEW%2BuJB_bvQLEaZCgoRTc1%3Di%2BQnrPPHxZ2%3D0SBSCyj9pkg%40mail.gmail.com

--
With Regards,
Amit Kapila.