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
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? |