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

[Roman Šindelář <roman.sindelar@xxxxxxxxx>]

Hello,

I am attaching the solution we used.
Thank you for your answers and help,
Roman

====================================================================

--

-- ALTER SUBSCRIPTION REFRESH TEST CASE / SOLUTION
--

-- 9. install extension dblink + create function/procedure [DESTINATION DATABASE]
\c db2 postgres
create extension dblink;

create function test.dblink_record_execute(TEXT, TEXT)
RETURNS SETOF record LANGUAGE c
PARALLEL RESTRICTED STRICT
AS '$libdir/dblink', $$dblink_record$$
;
 
create procedure test.dblink_refresh_subscription(sSubName VARCHAR, user_pwd text)
SECURITY DEFINER AS
$$
DECLARE
BEGIN
  perform test.dblink_record_execute(
        pg_catalog.format('user=%L dbname=%L port=%L password=%L', current_user, pg_catalog.current_database(), (SELECT setting FROM pg_catalog.pg_settings WHERE name = 'port'), user_pwd),
        pg_catalog.format('ALTER SUBSCRIPTION %I REFRESH PUBLICATION', sSubName)
    );
  raise notice 'Subscription % refreshed', sSubName;
END $$ LANGUAGE 'plpgsql';

grant execute on function test.dblink_record_execute(text,text) to usr_db_deploy;
grant  execute on procedure test.dblink_refresh_subscription(varchar,text) to usr_db_deploy;

-- 10. disable subscription [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.disable_subscription('test_sub');

-- 11. change: create new table [SOURCE DATABASE]
\c db1 usr_db_deploy
create table test.tab3 (id int primary key, num int);
grant select on table test.tab3 to usr_db_repl;
insert into test.tab3 values (3, 30);
select * from test.tab3;

-- 12. add table into publication [SOURCE DATABASE]
\c db1 usr_db_deploy
alter publication test_pub add table test.tab3;
select * from pg_publication_tables;

-- 13. create new table on destination side [DESTINATION DATABASE]
\c db2 usr_db_deploy
create table test.tab3 (id int primary key, num int);

-- 14. enable subscription [DESTINATION DATABASE]
\c db2 usr_db_deploy
call test.enable_subscription('test_sub');

-- 15. check new table [DESTINATION DATABASE]
-- Note: no rows, we need REFRESH PUBLICATION
\c db2 usr_db_deploy
select * from test.tab3;

-- 16. refresh subscription [DESTINATION DATABASE]
-- Note: run as non-superuser (usr_db_deploy)
\c db2 usr_db_deploy
call test.dblink_refresh_subscription('test_sub','');

====================================================================