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@xxxxxxxxxxx
