Hi Vignesh,
> > > 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
review-catalog-upgrade.spec
Description: Binary data
