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

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

Responses

Browse pgsql-general by date

  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.