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.klaver@aklaver.com