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

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Thomas Kellerer <shammat(at)gmx(dot)net>, "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 14:58:07
Message-ID: df9a3d36-25a4-d8a4-ae4c-8f2b6f6e49a9@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nitesh Nathani 2022-06-10 16:04:30 multiple entries for synchronous_standby_names
Previous Message Thomas Kellerer 2022-06-10 12:57:53 How to drop a subscription inside a stored procedure?