openGauss-server/src/test/regress/input/subscription.source-代码预览-openGauss-server:基于关系型数据库技术的企业级数据库管理系统项目 - AtomGit

a2a515e5创建于 2024年9月27日历史提交
--
-- SUBSCRIPTION
--
-- check help
\h CREATE SUBSCRIPTION
\h ALTER SUBSCRIPTION
\h DROP SUBSCRIPTION
--clear audit log
SELECT pg_delete_audit('1012-11-10', '3012-11-11');
--enable publication and subscription audit
\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_system_object=16777215" > /dev/null 2>&1
select pg_sleep(1);
--- prepare
\! echo $GAUSSHOME | xargs -I{} rm -f {}/bin/subscription.key.cipher
\! echo $GAUSSHOME | xargs -I{} rm -f {}/bin/subscription.key.rand
\! echo $GAUSSHOME | xargs -I{} @abs_bindir@/gs_guc generate -S 123456@pwd -D {}/bin -o subscription > /dev/null 2>&1 ; echo $?
\! echo $OLDGAUSSHOME | xargs -I{} rm -f {}/bin/subscription.key.cipher
\! echo $OLDGAUSSHOME | xargs -I{} rm -f {}/bin/subscription.key.rand
\! echo $OLDGAUSSHOME | xargs -I{} @abs_bindir@/gs_guc generate -S 123456@pwd -D {}/bin -o subscription > /dev/null 2>&1 ; echo $?
CREATE ROLE regress_subscription_user LOGIN SYSADMIN PASSWORD 'Abcdef@123';
CREATE ROLE regress_subscription_user2 LOGIN SYSADMIN PASSWORD 'Abcdef@123';
SET SESSION AUTHORIZATION 'regress_subscription_user' PASSWORD 'Abcdef@123';
DROP SUBSCRIPTION IF EXISTS testsub;
--- create subscription
-- fail - syntax error, no publications
CREATE SUBSCRIPTION testsub CONNECTION 'foo';
-- fail - syntax error, no connection
CREATE SUBSCRIPTION testsub PUBLICATION foo;
-- fail - could not connect to the publisher
create subscription testsub2 connection 'host=abc' publication pub;
create subscription testsub2 connection 'host=abc port=12345' publication pub;
set client_min_messages to error;
-- fail - syntax error, invalid connection string syntax: missing "="
CREATE SUBSCRIPTION testsub CONNECTION 'testconn' PUBLICATION testpub;
-- fail - unrecognized subscription parameter: create_slot
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (create_slot=false);
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (ENABLED=false, slot_name='testsub', synchronous_commit=off);
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (CONNECT=false, slot_name='testsub', synchronous_commit=off);
-- create SUBSCRIPTION with conninfo in two single quote, used to check mask string bug
CREATE SUBSCRIPTION testsub_maskconninfo CONNECTION 'host=''1.2.3.4'' port=''12345'' user=''username'' dbname=''postgres'' password=''password_1234''' PUBLICATION testpub WITH (ENABLED=false, slot_name='testsub', synchronous_commit=off);
CREATE SUBSCRIPTION testsub_maskconninfo CONNECTION 'host=''1.2.3.4'' port=''12345'' user=''username'' dbname=''postgres'' password=''password_1234''' PUBLICATION testpub WITH (CONNECT=false, slot_name='testsub', synchronous_commit=off);
-- fail - The number of host and port are inconsistent
create subscription sub1 connection 'dbname=postgres user=pubusr password=Huawei@123 host=192.168.0.38,192.168.0.38,192.168.0.38 port=14001,14501' publication pub1;
-- fail - a maximum of 9 servers are supported
create subscription sub1 connection 'dbname=postgres user=pubusr password=Huawei@123 host=192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38,192.168.0.38 port=14001,14501' publication pub1;
-- alter connection
ALTER SUBSCRIPTION testsub CONNECTION 'host=''1.2.3.4'' port=''12345'' user=''username'' dbname=''postgres'' password=''password_1234''';
ALTER SUBSCRIPTION testsub CONNECTION 'dbname=does_not_exist';
reset client_min_messages;
select subname, pg_get_userbyid(subowner) as Owner, subenabled, subconninfo, subpublications, subbinary from pg_subscription where subname='testsub';
--- alter subscription
------ set publication
ALTER SUBSCRIPTION testsub SET PUBLICATION testpub2, testpub3;
select subname, subenabled, subpublications from pg_subscription  where subname='testsub';
------ modify conninfo
ALTER SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist2';
select subname, subenabled, subconninfo from pg_subscription  where subname='testsub';
ALTER SUBSCRIPTION testsub SET (conninfo='dbname=doesnotexist3');
------ alter SUBSCRIPTION with conninfo in two single quote, used to check mask string bug
ALTER SUBSCRIPTION testsub_maskconninfo SET (conninfo='host=''1.2.3.4'' port=''12345'' user=''username'' dbname=''postgres'' password=''password_1234''', synchronous_commit=on);
select subname, subenabled, subconninfo from pg_subscription  where subname='testsub';
------ modify synchronous_commit
ALTER SUBSCRIPTION testsub SET (synchronous_commit=on);
select subname, subenabled, subsynccommit from pg_subscription  where subname='testsub';
------ modify slot_name to non-null value
------ fail - Currently enabled=false, cannot change slot_name to a non-null value.
ALTER SUBSCRIPTION testsub SET (slot_name='testsub');
-- alter owner
ALTER SUBSCRIPTION testsub owner to regress_subscription_user2;
-- alter subbinary to true
ALTER SUBSCRIPTION testsub SET (binary=true);
select subname, subbinary from pg_subscription where subname='testsub';
-- set subskiplsn
ALTER SUBSCRIPTION testsub SET (skiplsn = '0/ABCDEF');
select subname, subskiplsn from pg_subscription where subname='testsub';
ALTER SUBSCRIPTION testsub SET (skiplsn = '0/ABCDEFGH');
ALTER SUBSCRIPTION testsub SET (skiplsn = 'none');
select subname, subskiplsn from pg_subscription where subname='testsub';
-- set syncconninfo
ALTER SUBSCRIPTION testsub SET (syncconninfo = true);
select subname, subsyncconninfo from pg_subscription where subname='testsub';
ALTER SUBSCRIPTION testsub SET (syncconninfo = false);
select subname, subsyncconninfo from pg_subscription where subname='testsub';
-- disable test
ALTER SUBSCRIPTION testsub DISABLE;
--rename
ALTER SUBSCRIPTION testsub rename to testsub_rename;
--- inside a transaction block
------ CREATE SUBSCRIPTION ... WITH (enabled = true)
------ fail - ERROR:  CREATE SUBSCRIPTION ... WITH (enabled = true) cannot run inside a transaction block
BEGIN;
CREATE SUBSCRIPTION testsub_rename CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (ENABLED=true);
COMMIT;
-- -- active SUBSCRIPTION
BEGIN;
ALTER SUBSCRIPTION testsub_rename ENABLE;
select subname, subenabled from pg_subscription  where subname='testsub_rename';
ALTER SUBSCRIPTION testsub_rename SET (ENABLED=false);
select subname, subenabled from pg_subscription  where subname='testsub_rename';
COMMIT;
BEGIN;
ALTER SUBSCRIPTION testsub_rename REFRESH PUBLICATION;
COMMIT;

-- success, password len with 999
CREATE SUBSCRIPTION sub_len_999 CONNECTION 'host=192.16''''8.1.50 port=5432 user=foo dbname=foodb password=xxin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*lei' PUBLICATION insert_only WITH (connect = false);

-- fail, password len with 1000
CREATE SUBSCRIPTION sub_len_1000 CONNECTION 'host=192.16''''8.1.50 port=5432 user=foo dbname=foodb password=xxin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leid' PUBLICATION insert_only WITH (enabled = false);

-- fail, set password len with 1000
ALTER SUBSCRIPTION sub_len_999 SET (conninfo='host=192.16''''8.1.50 port=5432 user=foo dbname=foodb password=xxin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leid');
-- fail, set password len with 1000
ALTER SUBSCRIPTION sub_len_999 CONNECTION 'host=192.16''''8.1.50 port=5432 user=foo dbname=foodb password=xxin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leideyipi@123xin!@#$%^&*(!@#@$%^&*!@#$%^&*leid';

--- permission
CREATE ROLE normal_user LOGIN PASSWORD 'Abcdef@123';
SET SESSION AUTHORIZATION 'normal_user' PASSWORD 'Abcdef@123';
ALTER SUBSCRIPTION testsub_rename rename to testsub;

SET SESSION AUTHORIZATION 'regress_subscription_user' PASSWORD 'Abcdef@123';

--- drop subscription
DROP SUBSCRIPTION IF EXISTS testsub_rename;
DROP SUBSCRIPTION IF EXISTS testsub_maskconninfo;
DROP SUBSCRIPTION IF EXISTS sub_len_999;
--- cleanup
RESET SESSION AUTHORIZATION;
DROP ROLE regress_subscription_user;
DROP ROLE regress_subscription_user2;
DROP ROLE normal_user;

-- built-in function test
select pg_replication_origin_create('origin_test');
select pg_replication_origin_oid('origin_test');
select * from pg_replication_origin_status;
select pg_replication_origin_session_is_setup();
select pg_replication_origin_session_setup('origin_test');
select pg_replication_origin_session_is_setup();

create table t_origin_test(a int);
begin;
insert into t_origin_test values(1);
select pg_replication_origin_xact_setup('1/12345678', now());
commit;
select local_id,external_id,remote_lsn from pg_replication_origin_status;

select * from pg_replication_origin_progress('origin_test', false);
select * from pg_replication_origin_session_progress(false);
select local_id,external_id,remote_lsn from pg_show_replication_origin_status();

select pg_replication_origin_session_reset();
select pg_replication_origin_advance('origin_test', '1/87654321');

select pg_replication_origin_session_setup('origin_test');
select * from pg_replication_origin_session_progress(false);

select pg_replication_origin_xact_reset();
select pg_replication_origin_session_reset();

select pg_replication_origin_drop('origin_test');
-- error
select pg_replication_origin_session_setup('origin_test');
drop table t_origin_test;

SELECT object_name,detail_info FROM pg_query_audit('2022-01-13 9:30:00', '2031-12-12 22:00:00') where type = 'ddl_publication_subscription' order by time;
--clear audit log
SELECT pg_delete_audit('1012-11-10', '3012-11-11');

\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/datanode1/ -c "audit_system_object" > /dev/null 2>&1
select pg_sleep(1);