Re: Proposal: Conflict log history table for Logical Replication

On Sat, 20 Dec 2025 at 16:51, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Sat, Dec 20, 2025 at 3:17 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Tue, 16 Dec 2025 at 09:54, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > On Sun, 14 Dec 2025 at 21:17, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > >
> > > > On Sun, Dec 14, 2025 at 3:51 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Fri, Dec 12, 2025 at 3:04 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > On Thu, Dec 11, 2025 at 7:49 PM Dilip Kumar <dilipbalaut(at)gmail(dot)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.
> >
> > The attached v13 patch includes the fix for this issue. In addition,
> > it now raises an error when attempting to configure a conflict log
> > table that belongs to a temporary schema or is not a permanent
> > (persistent) relation.
>
> I have updated the patch and here are changes done
> 1. Splitted into 2 patches, 0001- for catalog related changes
> 0002-inserting conflict into the conflict table, Vignesh need to
> rebase the dump and upgrade related patch on this latest changes

Here is a rebased version of the dump/upgrade patch based on the v15
version posted at [1].
After replacing conflict_log_table with conflict_log_destination, we
don't specify a fully qualified table name directly. Instead, the
conflict log behavior is controlled via conflict_log_destination
(table, log, or all). Since pg_dump resets search_path, it must
explicitly set the schema in which the conflict log table should be
created or reused. To handle this, pg_dump temporarily sets and then
restores search_path around the ALTER SUBSCRIPTION ... SET
(conflict_log_destination ...) command, ensuring the conflict log
table is resolved in the intended schema.
Additionally, in non-upgrade dump/restore scenarios, the conflict log
table is not dumped as in non-upgrade mode it does not make sense to
link with the older conflict log table.

v15-0001 to v15-0004 is the same as the patches posted at [1].
dump/upgrade changes are present in v15-0005 patch.

[1] - https://www.postgresql.org/message-id/CAFiTN-uKn7mix8BkOOmJQ2cF5yKdfQUg2mX_w9vEC4787VZ_xQ%40mail.gmail.com

Regards.
Vignesh