ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function

Поиск
Список
Период
Сортировка
От Roman Šindelář
Тема ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
Дата
Msg-id CAE+_rYE7Xy4Ya1=7n0fHpU5WxTMEBEV81GWncQJi42APBdSnSQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function
Список 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@mail.gmail.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

В списке pgsql-general по дате отправления:

Предыдущее
От: hector vass
Дата:
Сообщение: Re: How to eliminate extra "NOT EXISTS"-query here?
Следующее
От: CG
Дата:
Сообщение: Off-label use for pg_repack