Re: How to drop a subscription inside a stored procedure? - Mailing list pgsql-general

From Thomas Kellerer
Subject Re: How to drop a subscription inside a stored procedure?
Date
Msg-id 76f2c021-1703-d6d1-39ac-e2d8d415234c@gmx.net
Whole thread Raw
In response to Re: How to drop a subscription inside a stored procedure?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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.





pgsql-general by date:

Previous
From: Shubham Mittal
Date:
Subject: Need optimization in query
Next
From: Rino Mardo
Date:
Subject: newbie db design question