Thread
-
Re: Proposal: Conflict log history table for Logical Replication
vignesh C <vignesh21@gmail.com> — 2025-12-23T06:15:48Z
On Sat, 20 Dec 2025 at 16:51, Dilip Kumar <dilipbalaut@gmail.com> wrote: > > On Sat, Dec 20, 2025 at 3:17 PM vignesh C <vignesh21@gmail.com> wrote: > > > > On Tue, 16 Dec 2025 at 09:54, vignesh C <vignesh21@gmail.com> wrote: > > > > > > 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. > > > > 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