Re: Proposal: Conflict log history table for Logical Replication

On Mon, Dec 15, 2025 at 2:55 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Mon, Dec 15, 2025 at 2:16 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Sun, Dec 14, 2025 at 9:20 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> > 4)
> > postgres=# SELECT c.relname FROM pg_depend d JOIN pg_class c ON c.oid
> > = d.objid JOIN pg_subscription s ON s.oid = d.refobjid WHERE s.subname
> > = 'sub1';
> > relname
> > ---------
> > clt
> >
> > postgres=# select count(*) from pg_shdepend where refobjid = (select
> > oid from pg_subscription where subname='sub1');
> > count
> > -------
> > 0
> >
> > Since dependency between sub and clt is a dependency involving
> > shared-object, shouldn't the entry be in pg_shdepend? Or do we allow
> > such entries in pg_depend as well?
>
> The primary reason for recording in pg_depend is that the
> RemoveRelations() function already includes logic to check for and
> report internal dependencies within pg_depends. Consequently, if we
> were to record the dependency in pg_shdepends, we would likely need to
> modify RemoveRelations() to incorporate handling for pg_shdepends
> dependencies.
>
> However, some might argue that when an object ID (objid) is local and
> the referenced object ID (refobjid) is shared, such as when a table is
> created under a ROLE, establishing a dependency with the owner, the
> dependency is currently recorded in pg_shdepend. In this scenario, the
> dependent object (the local table) can be dropped independently, while
> the referenced object (the shared owner) cannot.
>

Yes and same is true for tablespaces. Consider below case:
create tablespace tbs location <tbs_location>;
create table t2(c1 int, c2 int) PARTITION BY RANGE(c1) tablespace tbs;

>
> However, when aiming
> to record an internal dependency, the dependent object should not be
> droppable without first dropping the referencing object. Therefore, I
> believe the dependency record should be placed in pg_depend, as the
> depender is a local object and will check for dependencies there.
>

I think it make sense to add the dependency entry in pg_depend for
this case (dependent object table is db-local and referenced object
subscription is shared among cluster) as there is a fundamental
architectural difference between Tablespaces/Roles and Subscriptions
that determines why one needs pg_shdepend and the other is better off
with pg_depend.

It comes down to cross-database visibility during the DROP command.

1. The "Tablespace" Scenario (Why it needs pg_shdepend)
A Tablespace is a truly global resource. You can connect to postgres
(database A) and try to drop a tablespace that is being used by app_db
(database B).

The Problem: When you run DROP TABLESPACE tbs from Database A, the
system cannot look inside Database B's pg_depend to see if the
tablespace is in use. It would have to connect to every database in
the cluster to check.

The Solution: We explicitly push this dependency up to the global
pg_shdepend. This allows the DROP command in Database A to instantly
see: "Wait, object 123 in Database B needs this. Block the drop."

2. The "Subscription" Scenario (Why it does NOT need pg_shdepend)
Although pg_subscription is a shared catalog, a Subscription is pinned
to a specific database (subdbid). One can only DROP SUBSCRIPTION while
connected to the database that owns it. Consider a scenario where one
creates a subscription sub_1 in app_db. Now, one cannot connect to
postgres DB and run DROP SUBSCRIPTION sub_1. She must connect to
app_db. Since we need to conenct to app_db to drop the subscription,
the system has direct, fast access to the local pg_depend of app_db.
It doesn't need to consult a global "Cross-DB" catalog because there
is no mystery about where the dependencies live.

Does this theory sound more bullet-proof as to why it is desirable to
store dependency entries for this case in pg_depend. If so, I suggest
we can add some comments to explain the difference of subscription
with other shared objects in comments as the future readers may have
the same question.

--
With Regards,
Amit Kapila.