Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rsNMj-006ecM-4e for pgsql-general@arkaria.postgresql.org; Thu, 04 Apr 2024 13:42:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rsNMi-005Uph-68 for pgsql-general@arkaria.postgresql.org; Thu, 04 Apr 2024 13:42:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rsNMh-005UpY-QD for pgsql-general@lists.postgresql.org; Thu, 04 Apr 2024 13:42:51 +0000 Received: from mail-lf1-x12a.google.com ([2a00:1450:4864:20::12a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rsNMe-000bkz-Vf for pgsql-general@lists.postgresql.org; Thu, 04 Apr 2024 13:42:50 +0000 Received: by mail-lf1-x12a.google.com with SMTP id 2adb3069b0e04-516c5c39437so1248281e87.1 for ; Thu, 04 Apr 2024 06:42:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712238167; x=1712842967; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=kS8/8SehjM5DMF+NBvoG6bzJfU+30IKV9NjAp91x9MM=; b=SAQ9iFWYZT55OS9amL57jLtfSW9Z4YdEVeYAFVo4Gxo2eGwbyAJ5Avt0yW5Y0m+Uxc 9i+2OLPZb0uj1LgWng3tUQLzEqLoMTP34VBd0LyMT4wAHh9318lVD3xKNibmA/L/KUrS dpvsmcrm8qaVqrcgaJzzUPgEdjYn1EavP/rrhoOB3w7tw1KSqgV/5OME+NqJami+17OT XGbncWYTANy130i4a2iaiujAL3UyvTT+qGS77dKAENS2ALUXbRpeRiShxoVN5nndIOP2 2L+wVqsPglvXDPjwmDEo3QgVFv1IKIGFBIW5DpbMimGqg281s3pFZFi/cE+yOy80o1rW XUNw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712238167; x=1712842967; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=kS8/8SehjM5DMF+NBvoG6bzJfU+30IKV9NjAp91x9MM=; b=JDPfKB/Q1u8elq/CLQ9ZSGMgd6Ca3esGsS7bi7Mv+cYPSU6IPxGmXhhuw4ER5ZD2CO R5jx5CGYxqE7JdKgFlIGQD1HY4/PUygk4j5jt7AzqPaSzMAC5x/E3gzfFUQrY51gLDFk o/KlKdsLwHSJowXEQFOrctPn+gmBLgcYBZdeb7xUr6JIXP5dkjFEsPSCXIGDD4ApeJjH 6zDxH93AF+GcGlr0FC8kvE1gGkIQSIppaMW8rfzBqlZ88KOFft/L34ME96kV0rhH4i5E qyU1e9+Vz+IFX17qhxnApXhu5UQGEgRtZVd1rH9/8bLJNuVk3kYFluvdZSC7nOMm5+KM QCpg== X-Gm-Message-State: AOJu0Yz9DRtDyceD6nvSCcWeTntYFcmp/koReKzRmgMly2RSngxpb0gK FTogOEm2JLsHtuETa8njNI2AiGl21nDqfilOdLefE4qSnPP4pgl4VJ1bnuKeltux8YyjrECKTcK KOIG1ltM/MUORoR/cF9qm4VmezGwy/+GZe88= X-Google-Smtp-Source: AGHT+IHbBfAVfMCGJH/sBC58wrHHMNGgyzQ0xniCPVC0BGA1IRblhJfkgob2AFk6LPNGiEGIXb3ebZ6Bz60+2a9zD3E= X-Received: by 2002:ac2:5467:0:b0:516:a091:db5e with SMTP id e7-20020ac25467000000b00516a091db5emr1882940lfn.48.1712238167042; Thu, 04 Apr 2024 06:42:47 -0700 (PDT) MIME-Version: 1.0 From: =?UTF-8?B?Um9tYW4gxaBpbmRlbMOhxZk=?= Date: Thu, 4 Apr 2024 15:42:36 +0200 Message-ID: Subject: Re: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000b7bc7e061545837d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b7bc7e061545837d Content-Type: text/plain; charset="UTF-8" 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',''); ==================================================================== --000000000000b7bc7e061545837d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,
I am attaching the solution we used.<= br>Thank you for your answers and help,
Roman

=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

--
-- ALTER SUBSCRIPTION REFRESH TEST CASE / SOLUTION--

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

create function test.dblink_record_execute(TEXT, TEXT)
RETURNS SETO= F record LANGUAGE c
PARALLEL RESTRICTED STRICT
AS '$libdir/dblink= ', $$dblink_record$$
;
=C2=A0
create procedure test.dblink_re= fresh_subscription(sSubName VARCHAR, user_pwd text)
SECURITY DEFINER AS=
$$
DECLARE
BEGIN
=C2=A0 perform test.dblink_record_execute(=C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_catalog.format('user=3D%L dbname=3D%L p= ort=3D%L password=3D%L', current_user, pg_catalog.current_database(), (= SELECT setting FROM pg_catalog.pg_settings WHERE name =3D 'port'), = user_pwd),
=C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_catalog.format('ALTER SUBS= CRIPTION %I REFRESH PUBLICATION', sSubName)
=C2=A0 =C2=A0 );
=C2= =A0 raise notice 'Subscription % refreshed', sSubName;
END $$ LA= NGUAGE 'plpgsql';

grant execute on function test.dblink_reco= rd_execute(text,text) to usr_db_deploy;
grant=C2=A0 execute=C2=A0on 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_d= b_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 publicatio= n [SOURCE DATABASE]
\c db1 usr_db_deploy
alter publication test_pub a= dd table test.tab3;
select * from pg_publication_tables;

-- 13. c= reate 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
c= all test.enable_subscription('test_sub');

-- 15. check new t= able [DESTINATION DATABASE]
-- Note: no rows, we need REFRESH PUBLICATIO= N
\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','');

=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--000000000000b7bc7e061545837d--