From: | Roman Šindelář <roman(dot)sindelar(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function |
Date: | 2024-04-04 13:42:36 |
Message-ID: | CAE+_rYHd8gKWZ6zKy9RvvxaCeOWCC_e+YhcwjVYu4YjY0X8Csw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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','');
====================================================================
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2024-04-04 15:15:42 | Re: Failure of postgres_fdw because of TimeZone setting |
Previous Message | Thomas Nyberg | 2024-04-04 10:38:01 | What permissions are required for e.g. EXPLAIN UPDATE ... |