From: | Roman Šindelář <roman(dot)sindelar(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function |
Date: | 2023-11-28 16:01:57 |
Message-ID: | CAE+_rYE7Xy4Ya1=7n0fHpU5WxTMEBEV81GWncQJi42APBdSnSQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
we test database migration to new db servers from version 12 to 15 and a
problem with logical replication stopped us.
In the current code (PGSQL ver 12), we use a function with SECURITY DEFINER
for refreshing subscriptions:
---
DECLARE
BEGIN
execute 'alter subscription ' || sSubName || 'REFRESH PUBLICATION';
raise notice 'Subscription % refreshed', sSubName;
END
---
The function is called during a deployment of a new version of our
application when a deploy service account (NON-SUPERUSER) ENABLE and
REFRESH subscriptions at the end of the deploy.
In version 15, unfortunately, we get the following error:
ERROR: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
I found only one discussion about this problem, where as a workaround is
suggested using of dblink (
https://postgrespro.com/list/id/CANaTPsphRF+7k+YANMv8goGu3oQLY9XtACpkec8Ju=mr59GHGA(at)mail(dot)gmail(dot)com#head
)
Is there any other possibility or a recommendation to solve this case?
ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function in all
PGSQL versions 14+?
Thanks in advance for your help.
Roman Sindelar
From | Date | Subject | |
---|---|---|---|
Next Message | CG | 2023-11-28 16:46:38 | Off-label use for pg_repack |
Previous Message | Sri Mrudula Attili | 2023-11-28 14:57:52 | Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success. |