pgsql: Improve DROP SERVER handling of dependent subscriptions.

Improve DROP SERVER handling of dependent subscriptions.

We do not allow a DROP SERVER ... CASCADE to implicitly drop a
subscription, because it's in a shared catalog and dropping a
subscription has side effects. Instead we throw an error and the user
must drop the subscription explicitly. Document this behavior and add
a HINT to the error message.

Generalize AcquireDeletionLock()/ReleaseDeletionLock() to use shared
object locks for all shared catalogs, which includes AuthMemRelationId
and now SubscriptionRelationId.

Move error message after AcquireDeletionLock() to avoid an unnecessary
error if there's a concurrent DROP SUBSCRIPTION.

Addresses finding 10 & 15 in report from linked discussion.

Reported-by: Noah Misch <noah(at)leadboat(dot)com>
Discussion: https://postgr.es/m/20260710195902.4f.noahmisch@microsoft.com
Backpatch-through: 19

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b66ea17a3a1383a24096ba6a3fe1e83c5c059a35

Modified Files
--------------
doc/src/sgml/ref/drop_server.sgml | 4 ++++
src/backend/catalog/dependency.c | 31 +++++++++++++++++-------------
src/test/regress/expected/subscription.out | 4 ++++
src/test/regress/sql/subscription.sql | 2 ++
4 files changed, 28 insertions(+), 13 deletions(-)