Re: Publication DDL can race with a concurrent UPDATE

On Fri, 9 Oct 2026 at 15:16, Nikhil Sontakke <nikhil(at)planetscale(dot)com> wrote:
>
> Moving toward taking a stronger lock during DDL without penalizing DML sounds attractive.
>
> I tried v3 locally and found a lock-upgrade deadlock involving
> pg_publication, even when the publications cover disjoint schemas.

Thanks Nikhil for reporting the issue, I could reproduce the issue
with the steps provided.

> CreatePublication() already holds RowExclusiveLock on pg_publication when
> LockTablesWithoutReplicaIdentity() requests ShareLock on the same catalog.

The synchronization protocol in LockTablesWithoutReplicaIdentity() /
RelationBuildPublicationDesc() was using the ordinary relation lock on
pg_publication via LockRelationOid(). Any concurrent CREATE/ALTER/DROP
PUBLICATION already holds a RowExclusiveLock on the same relation
while modifying the catalog. Since RowExclusiveLock is
self-compatible, these catalog locks do not block each other directly.
However, when each session subsequently requests a ShareLock, that
lock conflicts with the other session's still-held RowExclusiveLock,
creating a deadlock cycle.

> Although two ShareLock requests are compatible, each conflicts with the
> other transaction's existing RowExclusiveLock.
>
> Here is a reproducer (the spec file is attached with this email as well):
>
> -- Setup
> CREATE SCHEMA review_a;
> CREATE TABLE review_a.t (id int PRIMARY KEY, val int);
>
> CREATE SCHEMA review_b;
> CREATE TABLE review_b.t (id int PRIMARY KEY, val int);
>
> CREATE TABLE review_outside (id int, val int);
> INSERT INTO review_outside VALUES (1, 1);
>
> -- Session 1
> BEGIN;
> UPDATE review_outside SET val = 2;
>
> Although review_outside is not published, its descriptor build acquires
> RowExclusiveLock on pg_publication because it lacks replica identity.
> That lock is retained until transaction end.
>
> -- Session 2
> CREATE PUBLICATION review_pb FOR TABLES IN SCHEMA review_b;
>
> Session 2 blocks requesting ShareLock on pg_publication while also holding
> the RowExclusiveLock acquired by CreatePublication().
>
> -- Session 1
> CREATE PUBLICATION review_pa FOR TABLES IN SCHEMA review_a;
>
> This deadlocks: Session 1 requests ShareLock while Session 2 holds
> RowExclusiveLock, and Session 2 is waiting for Session 1's RowExclusiveLock.
>
> The server reports both sessions waiting for ShareLock on relation
> (pg_publication), each blocked by the other. The same sequence completes
> without blocking on the unpatched build.
>
> This cycle involves only the publication catalog locks, so sorting the
> user-table locks does not address it.
>
> A dedicated synchronization lock would avoid colliding with ordinary catalog
> write locks and remove this additional lock-upgrade cycle.

Agreed, How about we use dedicated LockDatabaseObject() tag instead
of LockRelationOid():
/* LockTablesWithoutReplicaIdentity(), publicationcmds.c */
LockDatabaseObject(PublicationRelationId, InvalidOid, 0, ShareLock);

/* RelationBuildPublicationDesc(), relcache.c */
LockDatabaseObject(PublicationRelationId, InvalidOid, 0, RowExclusiveLock);

Using InvalidOid as the object ID is safe as a sentinel because it is
reserved and can never be a valid publication OID. Therefore, it
cannot conflict with AlterPublication()'s existing per-publication
lock on the same catalog.

However, that would
> not address the table/interlock deadlocks already acknowledged in the function
> comment.

Confirmed, and that is expected. The fix only removes the deadlock
cycle between operations that do not actually share any objects.
The attached top-up patch (on top of v3 patch shared at [1]) has the
changes for the same.
Thoughts?

Regards,
Vignesh