Re: Proposal: Conflict log history table for Logical Replication

On Sun, 14 Dec 2025 at 21:17, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Sun, Dec 14, 2025 at 3:51 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Fri, Dec 12, 2025 at 3:04 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > On Thu, Dec 11, 2025 at 7:49 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > > >
> > > > I was considering the interdependence between the subscription and the
> > > > conflict log table (CLT). IMHO, it would be logical to establish the
> > > > subscription as dependent on the CLT. This way, if someone attempts to
> > > > drop the CLT, the system would recognize the dependency of the
> > > > subscription and prevent the drop unless the subscription is removed
> > > > first or the CASCADE option is used.
> > > >
> > > > However, while investigating this, I encountered an error [1] stating
> > > > that global objects are not supported in this context. This indicates
> > > > that global objects cannot be made dependent on local objects.
> > > >
> > >
> > > What we need here is an equivalent of DEPENDENCY_INTERNAL for database
> > > objects. For example, consider following case:
> > > postgres=# create table t1(c1 int primary key);
> > > CREATE TABLE
> > > postgres=# \d+ t1
> > >                                            Table "public.t1"
> > >  Column |  Type   | Collation | Nullable | Default | Storage |
> > > Compression | Stats target | Description
> > > --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> > >  c1     | integer |           | not null |         | plain   |
> > >     |              |
> > > Indexes:
> > >     "t1_pkey" PRIMARY KEY, btree (c1)
> > > Publications:
> > >     "pub1"
> > > Not-null constraints:
> > >     "t1_c1_not_null" NOT NULL "c1"
> > > Access method: heap
> > > postgres=# drop index t1_pkey;
> > > ERROR:  cannot drop index t1_pkey because constraint t1_pkey on table
> > > t1 requires it
> > > HINT:  You can drop constraint t1_pkey on table t1 instead.
> > >
> > > Here, the PK index is created as part for CREATE TABLE operation and
> > > pk_index is not allowed to be dropped independently.
> > >
> > > > Although making an object dependent on global/shared objects is
> > > > possible for certain types of shared objects [2], this is not our main
> > > > objective.
> > > >
> > >
> > > As per my understanding from the above example, we need something like
> > > that only for shared object subscription and (internally created)
> > > table.
> >
> > Yeah that seems to be exactly what we want, so I tried doing that by
> > recording DEPENDENCY_INTERNAL dependency of CLT on subscription[1] and
> > it is behaving as we want[2].  And while dropping the subscription or
> > altering CLT we can delete internal dependency so that CLT get dropped
> > automatically[3]
> >
> > I will send an updated patch after testing a few more scenarios and
> > fixing other pending issues.
> >
> > [1]
> > +       ObjectAddressSet(myself, RelationRelationId, relid);
> > +       ObjectAddressSet(subaddr, SubscriptionRelationId, subid);
> > +       recordDependencyOn(&myself, &subaddr, DEPENDENCY_INTERNAL);
> >
> >
> > [2]
> > postgres[670778]=# DROP TABLE myschema.conflict_log_history2;
> > ERROR:  2BP01: cannot drop table myschema.conflict_log_history2
> > because subscription sub requires it
> > HINT:  You can drop subscription sub instead.
> > LOCATION:  findDependentObjects, dependency.c:788
> > postgres[670778]=#
> >
> > [3]
> > ObjectAddressSet(object, SubscriptionRelationId, subid);
> > performDeletion(&object, DROP_CASCADE
> >                            PERFORM_DELETION_INTERNAL |
> >                            PERFORM_DELETION_SKIP_ORIGINAL);
> >
> >
>
> Here is the patch which implements the dependency and fixes other
> comments from Shveta.

Thanks for the changes, the new implementation based on dependency
creates a cycle while dumping:
./pg_dump -d postgres -f dump1.txt -p 5433
pg_dump: warning: could not resolve dependency loop among these items:
pg_dump: detail: TABLE conflict  (ID 225 OID 16397)
pg_dump: detail: SUBSCRIPTION (ID 3484 OID 16396)
pg_dump: detail: POST-DATA BOUNDARY  (ID 3491)
pg_dump: detail: TABLE DATA t1  (ID 3485 OID 16384)
pg_dump: detail: PRE-DATA BOUNDARY  (ID 3490)

This can be seen with a simple subscription with conflict_log_table.
This was working fine with the v11 version patch.

Regards,
Vignesh