pgsql: Fix a deadlock during ALTER SUBSCRIPTION ... DROP PUBLICATION.

Fix a deadlock during ALTER SUBSCRIPTION ... DROP PUBLICATION.

A deadlock can occur when the DDL command and the apply worker acquire
catalog locks in different orders while dropping replication origins.

The issue is rare in PG16 and higher branches because, in most cases, the
tablesync worker performs the origin drop in those branches, and its
locking sequence does not conflict with DDL operations.

This patch ensures consistent lock acquisition to prevent such deadlocks.

As per buildfarm.

Reported-by: Alexander Lakhin <exclusion(at)gmail(dot)com>
Author: Ajin Cherian <itsajin(at)gmail(dot)com>
Reviewed-by: Hayato Kuroda <kuroda(dot)hayato(at)fujitsu(dot)com>
Reviewed-by: vignesh C <vignesh21(at)gmail(dot)com>
Reviewed-by: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Backpatch-through: 14, where it was introduced
Discussion: https://postgr.es/m/[email protected]

Branch
------
REL_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/41fb3f51cb74eca1ba959ccd2fb1c0d7fe0bf11b

Modified Files
--------------
src/backend/catalog/pg_subscription.c | 33 +++++++++++++++++++++++++----
src/backend/replication/logical/tablesync.c | 27 ++++++++++++++++++++---
src/include/catalog/pg_subscription_rel.h | 2 ++
3 files changed, 55 insertions(+), 7 deletions(-)