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

I am trying to write a stored procedure (Postgres 13) to enable
non-superusers to re-create a subscription.

For that, I essentially want to drop and re-create the subscription.
In order to be able to do that, the tables need to be empty.

So the approach is:

   Run a query to get all replicated tables, store this in an array.

   Run "drop subscription ..."

   Truncate all tables

   Run "create 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?

Regards
Thomas

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

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.