Re: How to drop a subscription inside a stored procedure?

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.