Re: Proposal: Conflict log history table for Logical Replication

On Wed, 10 Sept 2025 at 11:15, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> On Wed, Sep 10, 2025 at 3:25 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> >
>
...

> >
> > How about having this as a built-in type?
>
> Here we will have to create a built-in type of type table which is I
> think typcategory => 'C' and if we create this type it should be
> supplied with the "typrelid" that means there should be a backing
> catalog table. At least thats what I think.
>
A compound type can be used for building a table, it's not necessary to
create a table when creating the type. In user SQL:

CREATE TYPE conflict_log_type AS (
conflictid UUID,
subid OID,
tableid OID,
conflicttype TEXT,
operationtype TEXT,
replication_origin TEXT,
remote_commit_ts TIMESTAMPTZ,
local_commit_ts TIMESTAMPTZ,
ri_key JSON,
remote_tuple JSON,
local_tuple JSON
);

CREATE TABLE my_subscription_conflicts OF conflict_log_type;

...

>
> The one thing is not clear from Alastair's
> > response is that he said to make subscription as a dependency of
> > table, if we do so, then won't it be difficult to even drop
> > subscription and also doesn't that sound reverse of what we want.
>
> I assume he means subscription will be dependent on the log table,
> that means we can not drop the log table as subscription is dependent
> on this table.
>

Yes, that's what I was proposing.

> > > >>
> > > >> 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 costly operation 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 as a 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.
> > >
> >
> > But OTOH, there could be users who want such a table to be dropped.
> > One possibility is that if we user provided us a pre-created table
> > then we leave it to user to remove the table, otherwise, we can remove
> > with drop subscription.
>
> Thanks make sense.
>
> BTW, did we decide that we want a
> > conflict-table-per-subscription or one table for all subscriptions, if
> > later, then I guess the problem would be that it has to be a shared
> > table across databases.
>
> Right and I don't think there is an option to create a user defined
> shared table. And I don't think there is any issue creating per
> subscription conflict log history table, except that the subscription
> owner should have permission to create the table in the database while
> creating the subscription, but I think this is expected, either user
> can get the sufficient privilege or disable the option for conflict
> log history table.
>

Since subscriptions are created in a particular database, it seems
reasonable that error tables would also be created in a particular database.