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
