Adrian Klaver schrieb am 10.06.2022 um 16:58:
On 6/10/22 05:57, Thomas Kellerer wrote:
I am trying to write a stored procedure (Postgres 13) to enable
non-superusers to re-create a subscription.
However, the "drop subscription" part results in this error:
ERROR: DROP SUBSCRIPTION cannot be executed from a function
CONTEXT: SQL statement "drop subscription if exists my_replication"
From the docs:
https://www.postgresql.org/docs/current/sql-dropsubscription.html
"DROP SUBSCRIPTION cannot be executed inside a transaction block if the subscription is associated with a replication slot. (You can use ALTER SUBSCRIPTION to unset the slot.)"
I have not tested but you might try the ALTER SUBSCRIPTION first, though note the caveats here:
https://www.postgresql.org/docs/current/sql-altersubscription.html
Unsetting the slot works (after disabling the subscription), but then I have an "orphaned" slot on the publisher.
I don't see a way how I could get rid of that replication slot from the subscriber side.
