From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to drop a subscription inside a stored procedure? |
Date: | 2022-06-10 20:29:42 |
Message-ID: | 76f2c021-1703-d6d1-39ac-e2d8d415234c@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rino Mardo | 2022-06-11 07:05:15 | newbie db design question |
Previous Message | Shubham Mittal | 2022-06-10 19:18:00 | Need optimization in query |