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

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"
>
> I first thought that the initial SELECT to fetch all replicated tables,
> starts an implicit transaction, so I removed everything else from the procedure,
> including the dynamic SQL.
>
> But even this very simple implementation:
>
> create or replace procedure drop_subscription()
> as
> $$
> begin
> drop subscription if exists test_subscription;
> end;
> $$
> security definer
> language plpgsql;
>
>
> fails with that error.
>
> Is there any way, I can provide a stored procedure to do this?

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

>
> Regards
> Thomas
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com