> The root issue discussed here is that the relcache is stale due to insufficient > locking, and the standard approach to fixing such issues is to take a strong > lock during DDL rather than DML. The publication DDL case is special since it > needs to lock many tables, but I think we should build on that and improve > gradually rather than immediately strengthening the lock on the DML side. My > point is we should first put more effort into engineering the DDL side to solve > the issue, and if that turns out not to work, then we might consider the > DML-side approach as a last resort. > > Of course, testing is also worthwhile, but I think even if the results show > nothing, we still need broad consensus if we want to choose that approach.
Thanks, let's proceed with this approach. I found a few improvements that could be done:
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.
CreatePublication() already holds RowExclusiveLock on pg_publication when
LockTablesWithoutReplicaIdentity() requests ShareLock on the same catalog.
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. However, that would not address the table/interlock deadlocks already acknowledged in the function comment.
Regards, Nikhil --- Nikhil Sontakke PlanetScale Postgres Core Team
