From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | How to drop a subscription inside a stored procedure? |
Date: | 2022-06-10 12:57:53 |
Message-ID: | 3918eb37-2971-401f-4697-a716105cbcb1@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-06-10 14:58:07 | Re: How to drop a subscription inside a stored procedure? |
Previous Message | Amit Kapila | 2022-06-10 06:26:57 | Re: Support logical replication of DDLs |