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
------
REL_19_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/608704adee75fd4316a10f4aa9a93aa8c6dc7eeb

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(-)