测试用例

CREATE SCHEMA pgcrypto_2_text; SET search_path TO pgcrypto_2_text; /* ------------------------------------------------------------------------------------------------ * part 0: create some utils functions * ------------------------------------------------------------------------------------------------ */ CREATE FUNCTION strtoraw(string VARCHAR2) RETURN RAW IS BEGIN RETURN utl_raw.cast_to_raw(string); END; / CREATE FUNCTION rawtostr(rawdata RAW) RETURN VARCHAR2 IS BEGIN RETURN utl_raw.cast_to_varchar2(rawdata); END; / -- get data type name CREATE OR REPLACE FUNCTION oidgettype(typeoid OID) RETURNS TEXT AS $$ BEGIN RETURN (SELECT typname FROM pg_type WHERE oid = typeoid); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION oidgettype(typeoids OIDVECTOR) RETURNS TEXT[] AS $$ DECLARE result TEXT[] := '{}'; typoid OID; typename TEXT; BEGIN FOREACH typoid IN ARRAY typeoids LOOP SELECT typname INTO typename FROM pg_type WHERE oid = typoid; result := array_append(result, COALESCE(typename, 'Unknown OID')); END LOOP; RETURN result; END; $$ LANGUAGE plpgsql; -- find all functions from dbms_obfuscation_toolkit CREATE VIEW allfuncs AS SELECT proname name,pronargs args,oidgettype(prorettype) output,oidgettype(proargtypes) input FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dbms_obfuscation_toolkit') ORDER BY 1,3,2; /* ------------------------------------------------------------------------------------------------ * part 1: install pgcrypt with different versions * ------------------------------------------------------------------------------------------------ */ -- available version SELECT * FROM pg_available_extension_versions WHERE name = 'pgcrypto'; -- 2.0 (default version) CREATE EXTENSION pgcrypto; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; -- 1.2 (old version) CREATE EXTENSION pgcrypto WITH VERSION '1.2'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; -- 2.0 --> 1.2 CREATE EXTENSION pgcrypto; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; ALTER EXTENSION pgcrypto UPDATE TO '1.2'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; -- 1.2 --> 2.0 CREATE EXTENSION pgcrypto WITH VERSION '1.2'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; ALTER EXTENSION pgcrypto UPDATE TO '2.0'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; /* ------------------------------------------------------------------------------------------------ * part 2: core functions * ------------------------------------------------------------------------------------------------ */ CREATE EXTENSION pgcrypto; -- 1 desencrypt SELECT dbms_obfuscation_toolkit.desencrypt(strtoraw('11111111'), strtoraw('11111111')) FROM dual; SELECT dbms_obfuscation_toolkit.desencrypt(strtoraw('1111111111111111'), strtoraw('11111111')) FROM dual; -- 2 desdecrypt SELECT rawtostr(dbms_obfuscation_toolkit.desdecrypt(hextoraw('655EA628CF62585F'), strtoraw('11111111'))) FROM dual; SELECT rawtostr(dbms_obfuscation_toolkit.desdecrypt(hextoraw('655EA628CF62585F20D3B8A6E657876B'), strtoraw('11111111'))) FROM dual; -- 3 des3encrypt SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('11111111'), strtoraw('1111111122222222'), 0, strtoraw('11111111')) FROM dual; SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('1111111111111111'), strtoraw('1111111122222222'), 0, strtoraw('11111111')) FROM dual; -- 4 des3decrypt SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('0D28E17571051319'), strtoraw('1111111122222222'), 0, strtoraw('11111111'))) FROM dual; SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('0D28E17571051319ACCE2D19D9AED5C8'), strtoraw('1111111122222222'), 0, strtoraw('11111111'))) FROM dual; -- 5 des3encrypt -- without iv SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('11111111'), strtoraw('1111111122222222')) FROM dual; SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('1111111111111111'), strtoraw('1111111122222222')) FROM dual; -- 6 des3decrypt -- without iv SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('7934131269E334A2'), strtoraw('1111111122222222'))) FROM dual; SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('7934131269E334A2BCC64878CC5A075A'), strtoraw('1111111122222222'))) FROM dual; /* ------------------------------------------------------------------------------------------------ * part 3: different args * ------------------------------------------------------------------------------------------------ */ -- 1 des CREATE TABLE tplain (id INT, plain TEXT, key TEXT); INSERT INTO tplain VALUES (1, '', ''), (2, NULL, NULL), -- different plain (11, NULL, '88888888'), (12, '', '88888888'), (13, '1', '88888888'), (14, '7777777', '88888888'), (15, '88888888', '88888888'), (16, '999999999', '88888888'), (17, '17171717171717171', '88888888'), (18, '242424242424242422424242','88888888'), -- different key (31, '88888888', NULL), (32, '88888888', ''), (33, '88888888', '1'), (34, '88888888', '7777777'), (37, '88888888', '88888888'), (38, '88888888', '999999999'), (37, '88888888', '17171717171717171'), (38, '88888888', '242424242424242422424242'); CREATE TABLE tcipher (id INT, key TEXT, cipher RAW); INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 1; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 2; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 11; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 12; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 13; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 14; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 15; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 16; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 17; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 18; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 31; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 32; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 33; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 34; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 35; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 36; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 37; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 38; SELECT id,cipher,length(cipher) FROM tcipher ORDER BY id; SELECT id,rawtostr(dbms_obfuscation_toolkit.desdecrypt(cipher, strtoraw(key))) FROM tcipher ORDER BY id; DROP TABLE tplain,tcipher; -- 2 3des CREATE TABLE tplain (id INT, plain TEXT, keymod INT, key TEXT, iv TEXT); INSERT INTO tplain VALUES (1, '', 0, '', ''), (2, NULL, 0, NULL, NULL), -- different plain (11, NULL, 0, '1616161616161616', '88888888'), (12, '', 0, '1616161616161616', '88888888'), (13, '1', 0, '1616161616161616', '88888888'), (14, '7777777', 0, '1616161616161616', '88888888'), (15, '88888888', 0, '1616161616161616', '88888888'), (16, '999999999', 0, '1616161616161616', '88888888'), (17, '17171717171717171', 0, '1616161616161616', '88888888'), (18, '242424242424242422424242',0, '1616161616161616', '88888888'), -- different keymod (31, '1616161616161616', NULL, '1616161616161616', '88888888'), (32, '1616161616161616', -1, '1616161616161616', '88888888'), (33, '1616161616161616', -1.1, '1616161616161616', '88888888'), (34, '1616161616161616', 1.1, '1616161616161616', '88888888'), (35, '1616161616161616', 2, '1616161616161616', '88888888'), (36, '1616161616161616', '0', '1616161616161616', '88888888'), (37, '1616161616161616', 1, '242424242424242422424242', '88888888'), -- different key (51, '1616161616161616', 0, NULL, '88888888'), (52, '1616161616161616', 0, '', '88888888'), (53, '1616161616161616', 0, '1', '88888888'), (54, '1616161616161616', 0, '7777777', '88888888'), (55, '1616161616161616', 0, '88888888', '88888888'), (56, '1616161616161616', 0, '999999999', '88888888'), (57, '1616161616161616', 0, '17171717171717171', '88888888'), (58, '1616161616161616', 0, '242424242424242422424242', '88888888'), (59, '1616161616161616', 0, '272727272727272727272727272', '88888888'), (61, '1616161616161616', 1, NULL, '88888888'), (62, '1616161616161616', 1, '', '88888888'), (63, '1616161616161616', 1, '1', '88888888'), (64, '1616161616161616', 1, '7777777', '88888888'), (65, '1616161616161616', 1, '88888888', '88888888'), (66, '1616161616161616', 1, '999999999', '88888888'), (67, '1616161616161616', 1, '17171717171717171', '88888888'), (68, '1616161616161616', 1, '242424242424242422424242', '88888888'), (69, '1616161616161616', 1, '272727272727272727272727272', '88888888'), -- different iv (81, '1616161616161616', 1, '242424242424242422424242', NULL), (82, '1616161616161616', 1, '242424242424242422424242', ''), (83, '1616161616161616', 1, '242424242424242422424242', '1'), (84, '1616161616161616', 1, '242424242424242422424242', '7777777'), (85, '1616161616161616', 1, '242424242424242422424242', '88888888'), (86, '1616161616161616', 1, '242424242424242422424242', '999999999'), (87, '1616161616161616', 1, '242424242424242422424242', '17171717171717171'), (88, '1616161616161616', 1, '242424242424242422424242', '242424242424242422424242'), (89, '1616161616161616', 1, '242424242424242422424242', '272727272727272727272727272'); CREATE TABLE tcipher (id INT, CIPHER RAW, keymod INT, key TEXT, iv TEXT); INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 1; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 2; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 11; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 12; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 13; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 14; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 15; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 16; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 17; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 18; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 31; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 32; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 33; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 34; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 35; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 36; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 37; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 51; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 52; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 53; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 54; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 55; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 56; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 57; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 58; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 59; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 61; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 62; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 63; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 64; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 65; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 66; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 67; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 68; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 69; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 81; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 82; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 83; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 84; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 85; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 86; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 87; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 88; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 89; SELECT * FROM tcipher; SELECT id, rawtostr(dbms_obfuscation_toolkit.des3decrypt(cipher, strtoraw(key), keymod, strtoraw(iv))) FROM tcipher ORDER BY id; DROP TABLE tplain, tcipher; /* ------------------------------------------------------------------------------------------------ * part 4: other functions * ------------------------------------------------------------------------------------------------ */ SELECT length(dbms_obfuscation_toolkit.desgetkey(strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888'))) FROM dual; SELECT length(dbms_obfuscation_toolkit.des3getkey(0, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888'))) FROM dual; SELECT length(dbms_obfuscation_toolkit.des3getkey(1, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888'))) FROM dual; SELECT dbms_obfuscation_toolkit.md5(strtoraw('1111111111111111')) FROM dual; CREATE TABLE deskey(id INT, key RAW); CREATE TABLE des3key0 (id INT, key RAW); CREATE TABLE des3key1 (id INT, key RAW); INSERT INTO deskey SELECT generate_series(1, 1000), dbms_obfuscation_toolkit.desgetkey(strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); INSERT INTO des3key0 SELECT generate_series(1, 1000), dbms_obfuscation_toolkit.des3getkey(0, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); INSERT INTO des3key1 SELECT generate_series(1, 1000), dbms_obfuscation_toolkit.des3getkey(1, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); CREATE TABLE allcnt (cnt INT); INSERT INTO allcnt SELECT count(*) FROM deskey GROUP BY key; INSERT INTO allcnt SELECT count(*) FROM des3key0 GROUP BY key; INSERT INTO allcnt SELECT count(*) FROM des3key1 GROUP BY key; SELECT cnt,count(*) FROM allcnt GROUP BY cnt; -- test (need to spend too much time) -- CREATE TABLE moretime(id INT, key RAW); -- INSERT INTO moretime SELECT generate_series(1, 100000), -- dbms_obfuscation_toolkit.desgetkey(strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); -- -- 预期 key列重复的字符不多 -- SELECT * FROM moretime ORDER BY key ASC LIMIT 10; -- SELECT * FROM moretime ORDER BY key DESC LIMIT 10; -- DROP TABLE moretime; DROP TABLE deskey, des3key0, des3key1, allcnt; DROP EXTENSION pgcrypto; RESET search_path; DROP SCHEMA pgcrypto_2_text CASCADE;

1 CREATE/ALTER/DROP PROFILE 2 CREATE/ALTER USER .. PROFILE 3 验证profile生效 验证 failed_login_attempts 与 password_lock_time 验证 password_life_time 与 password_grace_time 验证 password_reuse_time 和 password_reuse_max 验证 idle_time 3 view CREATE PROFILE pro1 limit password_reuse_time 0.00002 password_reuse_max 10; -- 1.72s CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; ALTER USER u1 PASSWORD 'u1.pass2'; ALTER USER u1 PASSWORD 'u1.pass1'; -- error, interval < password_reuse_time \! sleep 2 ALTER USER u1 PASSWORD 'u1.pass1'; -- succeed, interval > password_reuse_time ALTER PROFILE pro1 LIMIT password_reuse_time 0.00005; -- 4.32s ALTER USER u1 PASSWORD 'u1.pass3'; ALTER USER u1 PASSWORD 'u1.pass4'; ALTER USER u1 PASSWORD 'u1.pass3'; -- error, interval < password_reuse_time \! sleep 5 ALTER USER u1 PASSWORD 'u1.pass3'; -- succeed, interval > password_reuse_time 1 CREATE/ALTER/DROP PROFILE -- check profile feature SHOW resource_limit; -- ================================ part 1 ================================ -- create profile CREATE PROFILE pro1; -- succeed: can set setting CREATE PROFILE pro2 LIMIT failed_login_attempts 10; -- succeed: can set more settings CREATE PROFILE pro3 LIMIT failed_login_attempts 10 password_lock_time 20 password_life_time 30 password_grace_time 40 password_reuse_time 50 password_reuse_max 60 idle_time 80; -- succeed: repeated settings are allowed CREATE PROFILE pro4 LIMIT password_life_time 10 password_life_time 20.2; -- succeed: setting value could be 'unlimited' CREATE PROFILE pro5 LIMIT failed_login_attempts unlimited password_lock_time 10 password_life_time unlimited; -- succeed: name max len is 64, name will be truncated CREATE PROFILE aaaaa111111111122222222223333333333444444444455555555556666666666; -- too long, max 64, wi -- truncated name: aaaaa1111111111222222222233333333334444444444555555555566666666 SELECT profile,count(*) FROM vb_profiles GROUP BY profile ORDER BY profile; SELECT * FROM vb_profiles WHERE profile = 'pro5' ORDER BY setname; -- error: profile name is invalid CREATE PROFILE 1; -- int CREATE PROFILE 1.1; -- numeric -- error: profile already exists CREATE PROFILE default LIMIT failed_login_attempts 10; CREATE PROFILE pro1 LIMIT failed_login_attempts 10; /* error: setting is invalid, supported settings: unit type min max ------------------------+---------+----------+---------+------------+---- failed_login_attempts | times | int | 1 | 2147483646 | 10 password_lock_time | days | double | 0.00001 | 24855 | 1 password_life_time | days | double | 0.00001 | 24855 | 180 password_grace_time | days | double | 0.00001 | 24855 | 7 password_reuse_time | days | double | 0.00001 | 24855 | 1 password_reuse_max | times | int | 1 | 2147483646 | 1 idle_time | minutes | double | 0.00001 | 2147483646 | 10 */ CREATE PROFILE epro2 LIMIT data_directory anyvalue; CREATE PROFILE epro3 LIMIT password_effect_time 10; CREATE PROFILE epro4 LIMIT 10 failed_login_attempts; -- error: setting lose value CREATE PROFILE epro5 LIMIT failed_login_attempts; CREATE PROFILE epro6 LIMIT failed_login_attempts 10 password_lock_time; -- error: setting value type is invalud CREATE PROFILE epro7 LIMIT failed_login_attempts 'iamstring'; CREATE PROFILE epro8 LIMIT failed_login_attempts 1.1; CREATE PROFILE epro9 LIMIT failed_login_attempts noint; CREATE PROFILE epro10 LIMIT failed_login_attempts default; -- error: setting value is out of limit CREATE PROFILE epro11 LIMIT failed_login_attempts 0; CREATE PROFILE epro12 LIMIT failed_login_attempts 1 password_lock_time 0; CREATE PROFILE epro13 LIMIT password_lock_time 24856; CREATE PROFILE epro14 LIMIT password_lock_time 0.000001; SELECT profile,count(*) FROM vb_profiles GROUP BY profile ORDER BY profile; DROP PROFILE pro1, pro2, pro3, pro4, pro5; DROP PROFILE aaaaa111111111122222222223333333333444444444455555555556666666666; -- ================================ part 2 ================================ -- alter profile CREATE PROFILE pro1; CREATE PROFILE pro2 LIMIT failed_login_attempts 100; CREATE PROFILE pro3 LIMIT password_lock_time 20; -- succeed: can set setting ALTER PROFILE pro1 LIMIT failed_login_attempts 1000; -- succeed: can set more settings ALTER PROFILE pro2 LIMIT failed_login_attempts 10 password_lock_time 20 password_life_time 30 password_grace_time 40 password_reuse_time 50 password_reuse_max 60 idle_time 80; -- succeed: setting value could be 'unlimited' ALTER PROFILE pro3 LIMIT failed_login_attempts unlimited password_lock_time 10 password_life_time unlimited; -- succeed: can alter default profile ALTER PROFILE default LIMIT failed_login_attempts 80 password_reuse_time 70.3; SELECT * FROM vb_profiles ORDER BY profile,setname; -- error: profile name is invalid ALTER PROFILE 1 LIMIT failed_login_attempts 10; -- int ALTER PROFILE 1.1 LIMIT failed_login_attempts 10; -- numeric ALTER PROFILE aaaaa111111111122222222223333333333444444444455555555556666666666 LIMIT failed_login_attempts 10; -- too long, max 64 -- error: repeated settings are not allowed ALTER PROFILE pro3 LIMIT password_reuse_time 10 password_reuse_time 20.2; -- error: profile doesn't exist ALTER PROFILE epro1 LIMIT failed_login_attempts 10; ALTER PROFILE epro2 LIMIT failed_login_attempts 10; /* error: setting is invalid */ ALTER PROFILE pro1 LIMIT data_directory anyvalue; ALTER PROFILE pro1 LIMIT password_effect_time 10; ALTER PROFILE pro1 LIMIT 10 failed_login_attempts; -- error: setting lose value ALTER PROFILE pro1 LIMIT failed_login_attempts; ALTER PROFILE pro1 LIMIT failed_login_attempts 10 password_lock_time; -- error: setting value type is invalud ALTER PROFILE pro1 LIMIT failed_login_attempts 'iamstring'; ALTER PROFILE pro1 LIMIT failed_login_attempts 1.1; ALTER PROFILE pro1 LIMIT failed_login_attempts noint; ALTER PROFILE pro1 LIMIT failed_login_attempts default; -- error: setting value is out of limit ALTER PROFILE pro1 LIMIT failed_login_attempts 0; ALTER PROFILE pro1 LIMIT failed_login_attempts 1 password_lock_time 0; ALTER PROFILE pro1 LIMIT password_lock_time 24856; ALTER PROFILE pro1 LIMIT password_lock_time 0.000001; DROP PROFILE pro1, pro2, pro3; -- ================================ part 3 ================================ -- drop profile CREATE PROFILE pro1; CREATE PROFILE pro2 LIMIT failed_login_attempts 100; CREATE PROFILE pro3 LIMIT password_lock_time 20; CREATE PROFILE pro4; CREATE PROFILE pro5; -- succeed: drop profile DROP PROFILE pro1; DROP PROFILE IF EXISTS pro1; DROP PROFILE IF EXISTS pro1, pro2, pro3; SELECT profile,count(*) FROM vb_profiles GROUP BY profile; -- error: profile doesn't exist DROP PROFILE pro0; DROP PROFILE pro1, pro4, pro5; -- error: default profile can't be dropped DROP PROFILE default; DROP PROFILE pro4, default; DROP PROFILE IF EXISTS pro4, pro5; 2 CREATE/ALTER USER .. PROFILE -- ================================ part 4 ================================ -- set profile for user SELECT * FROM vb_user_profiles; CREATE PROFILE pro1; CREATE PROFILE pro2; -- succeed: set profile for user CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; CREATE USER u2 PASSWORD 'u2.pass1' PROFILE pro1; CREATE USER u3 PASSWORD 'u3.pass1' PROFILE default; CREATE USER u4 PASSWORD 'u4.pass1'; -- error: profile doesn't exist CREATE USER eu1 PASSWORD 'u4.pass1' PROFILE pro1000; -- error: profile name invalid CREATE USER eu2 PASSWORD 'u4.pass1' PROFILE 1; CREATE USER eu3 PASSWORD 'u4.pass1' PROFILE; SELECT * FROM vb_user_profiles; DROP USER IF EXISTS u1, u2, u3, u4; -- catalog also changed SELECT * FROM vb_user_profiles; DROP PROFILE IF EXISTS pro1, pro2; -- ================================ part 5 ================================ -- alter user profile CREATE PROFILE pro1; CREATE PROFILE pro2; CREATE USER u1 PASSWORD 'u1.pass1'; CREATE USER u2 PASSWORD 'u2.pass1' PROFILE pro2; CREATE USER u3 PASSWORD 'u3.pass1' PROFILE default; -- succeed: alter user set profile ALTER USER u1 PROFILE pro1; ALTER USER u2 PROFILE default; ALTER USER u3 PROFILE pro1; -- error: profile doesn't exist ALTER USER u1 PROFILE pro1000; -- error: profile name invalid ALTER USER u1 PROFILE 1; ALTER USER u1 PROFILE; SELECT * FROM vb_user_profiles ORDER BY roloid; DROP USER IF EXISTS u1, u2, u3; DROP PROFILE IF EXISTS pro1, pro2; -- ================================ part 6 ================================ -- drop user and profile CREATE PROFILE pro1; CREATE PROFILE pro2; CREATE PROFILE pro3; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; CREATE USER u2 PASSWORD 'u2.pass1' PROFILE pro2; -- succeed: drop user SELECT * FROM vb_user_profiles WHERE rolname = 'u1'; DROP USER u1; SELECT * FROM vb_user_profiles WHERE rolname = 'u1'; -- error: can't drop profile if any user are using it DROP PROFILE pro2; -- error: default profile can't be dropped DROP PROFILE default; DROP PROFILE pro3, default; DROP PROFILE default, pro3; SELECT * FROM vb_user_profiles ORDER BY roloid; DROP USER IF EXISTS u1, u2; DROP PROFILE IF EXISTS pro1, pro2, pro3; 3 验证profile生效 验证 failed_login_attempts 与 password_lock_time CREATE PROFILE pro1 LIMIT failed_login_attempts 3 password_lock_time 0.0001; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; -- 运行登录失败3次，失败后锁定8秒 CREATE USER u2 PASSWORD 'u2.pass1'; -- 允许登录失败无数次 vsql -p 5432 -d postgres -U u1 -W "u1.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u1 -W "u1.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u1 -W "u1.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- u2 vsql -p 5432 -d postgres -U u2 -W "u2.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" vsql -p 5432 -d postgres -c "DROP USER u1,u2" 验证 password_life_time 与 password_grace_time CREATE PROFILE pro2 LIMIT password_life_time 0.0001 -- 8s password_grace_time 0.0001; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro2; -- 密码有效期为8s，密码宽容期也为8s CREATE USER u2 PASSWORD 'u2.pass1'; -- 密码有效期无限制 \q vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" 验证 password_reuse_time 和 password_reuse_max CREATE PROFILE pro3 LIMIT password_reuse_time 0.0001 -- 不能用过去8秒使用过的密码 password_reuse_max 100; -- 不能用过去100次使用过的 DROP USER IF EXISTS u1,u2; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro3; ALTER USER u1 PASSWORD 'u1.pass2'; ALTER USER u1 PASSWORD 'u1.pass1'; SELECT pg_sleep(3); ALTER USER u1 PASSWORD 'u1.pass1'; SELECT pg_sleep(3); ALTER USER u1 PASSWORD 'u1.pass1'; SELECT pg_sleep(3); ALTER USER u1 PASSWORD 'u1.pass1'; CREATE USER u2 PASSWORD 'u2.pass1'; ALTER USER u2 PASSWORD 'u2.pass2'; ALTER USER u2 PASSWORD 'u2.pass1'; ALTER USER u2 PASSWORD 'u2.pass2'; ALTER USER u2 PASSWORD 'u2.pass1'; -- ----------------------------- CREATE PROFILE pro4 LIMIT password_reuse_max 2 -- 不能用过去2次使用过的 password_reuse_time 20; DROP USER IF EXISTS u1,u2; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro4; ALTER USER u1 PASSWORD 'u1.pass2'; ALTER USER u1 PASSWORD 'u1.pass3'; ALTER USER u1 PASSWORD 'u1.pass4'; ALTER USER u1 PASSWORD 'u1.pass3'; -- error ALTER USER u1 PASSWORD 'u1.pass2'; -- ok 验证 idle_time CREATE PROFILE pro5 LIMIT idle_time 0.1; -- 6s DROP USER IF EXISTS u1,u2; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro5; CREATE USER u2 PASSWORD 'u2.pass1'; \q vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -r SELECT 1; -- 不用pg_sleep \! sleep 7 SELECT 1; \q vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -r SELECT 1; -- 不用pg_sleep \! sleep 7 SELECT 1; 3 view CREATE OR REPLACE VIEW SYS.DBA_PROFILES AS SELECT profile "PROFILE", setname "RESOURCE_NAME", setkind "RESOURCE_TYPE", setval "LIMIT" FROM vb_profiles; 升级 审计 oid knl_u_misc_init AuthenticatedUserId = InvalidOid ResetStreamStatus AuthenticatedUserId = InvalidOid InitializeSessionUserId AuthenticatedUserId = SearchSysCache(rolname / useroid) InitializeSessionUserIdStandalone AuthenticatedUserId = BOOTSTRAP_SUPERUSERID SessionUserId CurrentUserId create_session_context # get_setting_from_guc_or_profile enable_session_sig_alarm # get_setting_from_guc_or_profile ReadCommand # get_setting_from_guc_or_profile enable_session_sig_alarm # get_setting_from_guc_or_profile disable_session_sig_alarm TpoolSchedulerMain CheckSessionTimeout # get_setting_from_guc_or_profile handle_sig_alarm CheckSessionTimeout # get_setting_from_guc_or_profile WaitMission # get_setting_from_guc_or_profile enable_session_sig_alarm

1 本地自测 1.1 快速tpcc vkill rm -rf $GAUSSHOME/data* # cp -r ~/w100_t200/data $GAUSSHOME/data # cp -r ~/w100_t200/data1 $GAUSSHOME/ cp -r ~/w5/data $GAUSSHOME/data cp -r ~/w5/data1 $GAUSSHOME/ # cp -r ~/w100/data $GAUSSHOME/data # cp -r ~/w100/data1 $GAUSSHOME/ # vb_guc set -D $GAUSSHOME/data -c "enable_ddl_logical_decode=on" # vb_guc set -D $GAUSSHOME/data1 -c "enable_ddl_logical_decode=on" # vb_guc set -D $GAUSSHOME/data1 -c "max_replication_slots=50" # vb_guc set -D $GAUSSHOME/data1 -c "max_background_workers=50" # vb_guc set -D $GAUSSHOME/data1 -c "max_logical_replication_workers=50" vb_guc set -D $GAUSSHOME/data1 -c "max_connections=4000" vb_guc set -D $GAUSSHOME/data1 -c "enable_audit=on" vb_guc set -D $GAUSSHOME/data1 -c "audit_buffer_size=262144" vb_guc set -D $GAUSSHOME/data1 -c "audit_directory_size=1073741824" vb_ctl start -D $GAUSSHOME/data vb_ctl start -D $GAUSSHOME/data1 # 五、发布端（发布） vsql -d pubdb -p 64000 -c "CREATE PUBLICATION pub1 FOR all tables WITH (ddl='all');" vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" # # 六、订阅端（订阅） gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription vsql -d pubdb -p 65000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=220);" # # vsql -d pubdb -p 65000 -r # \q # cd ~/tpcc # source envbm # ./toolbm run vpub # 换窗口查询 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" vsql -d pubdb -p 64000 -c "SELECT * FROM vb_parallel_apply_stat()" vsql -d pubdb -p 64000 -c "SELECT * FROM vb_parallel_apply_stat()" # 场景一：验证内存 vsql -d pubdb -p 64000 -c "SELECT * FROM gs_thread_memory_context ORDER BY totalsize DESC limit 30;" > start_pub.mem vsql -d pubdb -p 64000 -c "SELECT * FROM gs_shared_memory_detail ORDER BY totalsize DESC limit 30;;" >> start_pub.mem vsql -d pubdb -p 65000 -c "SELECT * FROM gs_thread_memory_context ORDER BY totalsize DESC limit 30;;" > start_sub.mem vsql -d pubdb -p 65000 -c "SELECT * FROM gs_shared_memory_detail ORDER BY totalsize DESC limit 30;;" >> start_sub.mem # 场景八：验证数据一致性 vsql -d pubdb -p 64000 -c "DROP TABLE IF EXISTS tcnt" vsql -d pubdb -p 64000 -c "CREATE TABLE tcnt (c1 TEXT,c2 INT);" vsql -d pubdb -p 65000 -c "DROP TABLE IF EXISTS tcnt" vsql -d pubdb -p 65000 -c "CREATE TABLE tcnt (c1 TEXT,c2 INT);" for t in bmsql_config bmsql_customer bmsql_district bmsql_history bmsql_item bmsql_new_order bmsql_oorder bmsql_order_line bmsql_stock bmsql_warehouse; do echo "$t" vsql -d pubdb -p 64000 -c "INSERT INTO tcnt VALUES('$t', (SELECT count(1) FROM $t));" done; for t in bmsql_config bmsql_customer bmsql_district bmsql_history bmsql_item bmsql_new_order bmsql_oorder bmsql_order_line bmsql_stock bmsql_warehouse; do echo "$t" vsql -d pubdb -p 65000 -c "INSERT INTO tcnt VALUES('$t', (SELECT count(1) FROM $t));" done; vsql -d pubdb -p 64000 -c "SELECT * FROM tcnt" vsql -d pubdb -p 65000 -c "SELECT * FROM tcnt;" # 场景一：重启订阅端（kill） for i in $(seq 0 10); do echo "$i------------------------------------------------------------------------------------" ps ux | grep 'vastbase.*-D.*/data1' | grep -v grep | awk '{print $2}' | xargs kill -9 vb_ctl start -D $GAUSSHOME/data1 sleep 7 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" >> a.out done; # 场景二：重启订阅端(stop) for i in $(seq 0 10); do echo "$i------------------------------------------------------------------------------------" echo "$i------------------------------------------------------------------------------------" >> a.out vb_ctl stop -D $GAUSSHOME/data1 vb_ctl start -D $GAUSSHOME/data1 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" >> a.out sleep 7 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" >> a.out done; # 场景三：重启发布端 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" vb_ctl stop -D $GAUSSHOME/data sleep 5 vb_ctl start -D $GAUSSHOME/data vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" vb_ctl restart -D $GAUSSHOME/data1 # 场景三：重建 subscription for i in $(seq 0 10); do echo "$i------------------------------------------------------------------------------------" echo "$i------------------------------------------------------------------------------------" >> a.out vsql -d pubdb -p 65000 -c "DROP SUBSCRIPTION sub1;" vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" vsql -d pubdb -p 65000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=20);" sleep 6 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" >> a.out done; vsql -d pubdb -p 65000 -c "DROP SUBSCRIPTION sub1;" vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" vsql -d pubdb -p 65000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=20);" vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" vsql -d pubdb -p 65000 -c "SELECT * FROM pg_subscription_rel" vsql -d pubdb -p 64000 -c "SELECT * FROM vb_parallel_apply_stat()" SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / (1024 * 1024); # 场景五：ddl vsql -d pubdb -p 64000 -c "\d" vsql -d pubdb -p 65000 -c "\d" vsql -d pubdb -p 64000 -c "DROP TABLE bmsql_warehouse" vsql -d pubdb -p 64000 -c "CREATE TABLE t3(c1 INT)" vsql -d pubdb -p 64000 -c "\d" vsql -d pubdb -p 65000 -c "\d" # 场景六：多个发布订阅 vsql -d postgres -p 64000 -c "CREATE DATABASE db3;" vsql -d db3 -p 64000 -c "CREATE TABLE t3(c1 INT PRIMARY KEY, c2 TEXT);" vsql -d db3 -p 64000 -c "CREATE PUBLICATION pub3 FOR all tables WITH (ddl='all');" vsql -d db3 -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot3', 'pgoutput');" vsql -d postgres -p 65000 -c "CREATE DATABASE db3;" vsql -d db3 -p 65000 -c "CREATE TABLE t3(c1 INT PRIMARY KEY, c2 TEXT);" vsql -d db3 -p 65000 -c "CREATE SUBSCRIPTION sub3 CONNECTION 'host=172.16.103.90 port=64001 dbname=db3 user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub3 WITH (create_slot=false, slot_name = 'pslot3', copy_data = false, worker_number=20);" vsql -d pubdb -p 65000 -c "DROP SUBSCRIPTION sub1;" for i in $(seq 0 10); do vsql -d db3 -p 64000 -c "BEGIN; INSERT INTO t3 VALUES($i,'a'); UPDATE t3 SET c2='b' WHERE c1=$i-1; COMMIT;" & done; wait # 跑10分钟 ./toolbm run vpub vsql -d pubdb -p 65000 -c "DROP SUBSCRIPTION sub1;" # for t in bmsql_config bmsql_customer bmsql_district bmsql_history bmsql_item bmsql_new_order bmsql_oorder bmsql_order_line bmsql_stock bmsql_warehouse; do echo "$t" vsql -d pubdb -p 65000 -c "TRUNCATE $t" done; 续点重传 vkill rm -rf $GAUSSHOME/data* # cp -r ~/w5/data $GAUSSHOME/data # cp -r ~/w5/data1 $GAUSSHOME/ cp -r ~/w100/data $GAUSSHOME/data cp -r ~/w100/data1 $GAUSSHOME/ vb_guc set -D $GAUSSHOME/data1 -c "max_connections=4000" vb_ctl start -D $GAUSSHOME/data vb_ctl start -D $GAUSSHOME/data1 # 五、发布端（发布） vsql -d pubdb -p 64000 -c "CREATE PUBLICATION pub1 FOR all tables WITH (ddl='all');" vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" # # 六、订阅端（订阅） gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription vsql -d pubdb -p 65000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=220);" # # vsql -d pubdb -p 65000 -r # \q vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" vsql -d pubdb -p 64000 -c "SELECT * FROM vb_parallel_apply_stat()" # ./toolbm run vpub # 重启订阅端（kill） for i in $(seq 0 10); do echo "$i------------------------------------------------------------------------------------" ps ux | grep 'vastbase.*-D.*/data1' | grep -v grep | awk '{print $2}' | xargs kill -9 vb_ctl start -D $GAUSSHOME/data1 sleep 7 vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" >> a.out done; vsql -d pubdb -p 64000 -c " SELECT * FROM pg_get_replication_slots(); " vsql -d pubdb -p 64000 -c " SELECT pg_current_wal_lsn();" # 验证数据一致性 vsql -d pubdb -p 64000 -c "DROP TABLE IF EXISTS tcnt" vsql -d pubdb -p 64000 -c "CREATE TABLE tcnt (c1 TEXT,c2 INT);" vsql -d pubdb -p 65000 -c "DROP TABLE IF EXISTS tcnt" vsql -d pubdb -p 65000 -c "CREATE TABLE tcnt (c1 TEXT,c2 INT);" for t in bmsql_config bmsql_customer bmsql_district bmsql_history bmsql_item bmsql_new_order bmsql_oorder bmsql_order_line bmsql_stock bmsql_warehouse; do echo "$t" vsql -d pubdb -p 64000 -c "INSERT INTO tcnt VALUES('$t', (SELECT count(1) FROM $t));" done; for t in bmsql_config bmsql_customer bmsql_district bmsql_history bmsql_item bmsql_new_order bmsql_oorder bmsql_order_line bmsql_stock bmsql_warehouse; do echo "$t" vsql -d pubdb -p 65000 -c "INSERT INTO tcnt VALUES('$t', (SELECT count(1) FROM $t));" done; vsql -d pubdb -p 64000 -c "SELECT * FROM tcnt" vsql -d pubdb -p 65000 -c "SELECT * FROM tcnt;" -- ===================== worker_number ======================= CREATE SUBSCRIPTION sub1 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false); DROP SUBSCRIPTION sub1; \! vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" CREATE SUBSCRIPTION sub2 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=1); DROP SUBSCRIPTION sub2; \! vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" CREATE SUBSCRIPTION sub3 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=2); DROP SUBSCRIPTION sub3; \! vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" CREATE SUBSCRIPTION sub4 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=200); DROP SUBSCRIPTION sub4; \! vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" CREATE SUBSCRIPTION sub5 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=400); DROP SUBSCRIPTION sub5; CREATE SUBSCRIPTION sub6 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=1000); DROP SUBSCRIPTION sub6; -- ===================== copy_data ======================= -- do truncate fist CREATE SUBSCRIPTION sub21 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = true, worker_number=50); SELECT * FROM pg_subscription_rel; DROP SUBSCRIPTION sub21; -- copy and stop -- ===================== cache file ======================= -- 1. drop subscription -- 2. kill progress -- ===================== 断点续传 ======================= vsql -d postgres -p 50000 -c "DROP TABLE IF EXISTS t1" vsql -d postgres -p 50000 -c "CREATE TABLE t1(c1 INT, c2 TEXT)" for i in $(seq 0 10); do vsql -d postgres -p 50000 -c "BEGIN; INSERT INTO t1 VALUES($i,'a'); UPDATE t1 SET c2='b' WHERE c1=$i-1; COMMIT;" & done vsql -d postgres -p 50000 -c "SELECT * FROM t1 ORDER BY c1" parallel_apply.source 1 自测 批量验证 初始化模板 export VDATA=~/g100/rep/data vb_guc set -D $VDATA -c "port=64000" vb_guc set -D $VDATA -c "max_connection=2000" vb_guc set -D $VDATA -c "wal_level=logical" vb_guc set -D $VDATA -c "max_replication_slots=100" vb_guc set -D $VDATA -c "max_background_workers=100" vb_guc set -D $VDATA -c "max_logical_replication_workers=100" sed -i '1i host all all 0.0.0.0/0 sha256\n' $VDATA/pg_hba.conf echo "host replication all 0.0.0.0/0 sha256" >> $VDATA/pg_hba.conf insert性能 export VDATA=~/g100/rep/data vkill rm -rf ~/g100/rep/data cp -r ~/g100/rep/temp ~/g100/rep/data vstart vsql -d postgres -p 64000 -r -- 3 create publicatioin \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables; CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput'); -- sub CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name='pslot1', copy_data=false, worker_number=80); SELECT count(1) FROM t1; SELECT * FROM vb_parallel_apply_stat(); \c pubdb truncate t1; \c subdb truncate t1; SELECT datname,state,length(query) FROM pg_stat_activity WHERE datname = 'pubdb'; 多个发布订阅 vb_guc set -D ~/g100/rep/data -c "max_replication_slots=256" vb_guc set -D ~/g100/rep/data -c "max_background_workers=256" vb_guc set -D ~/g100/rep/data -c "max_logical_replication_workers=256" export VDATA=~/g100/rep/data vkill rm -rf ~/g100/rep/data cp -r ~/g100/rep/temp ~/g100/rep/data vb_guc set -D ~/g100/rep/data -c "enable_ddl_logical_decode=on" vb_guc set -D ~/g100/rep/data -c "max_wal_senders=36" vb_guc set -D ~/g100/rep/data -c "max_replication_slots=256" vb_guc set -D ~/g100/rep/data -c "max_background_workers=256" vb_guc set -D ~/g100/rep/data -c "max_logical_replication_workers=256" vb_guc set -D ~/g100/rep/data -c "max_connections=1000" vstart rm -rf ~/g100/rep/data1 vb_guc set -D ~/g100/rep/data1 -c "port=65000" cp -r ~/g100/rep/temp ~/g100/rep/data1 vb_ctl start -D ~/g100/rep/data1 # 发布 vsql -d postgres -p 64000 -c "CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345';" for i in $(seq 0 11); do vsql -d postgres -p 64000 -c "CREATE DATABASE pubdb$i;" vsql -d pubdb$i -p 64000 -c "CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT);" vsql -d pubdb$i -p 64000 -c "CREATE PUBLICATION pub$i FOR all tables;" vsql -d pubdb$i -p 64000 -c "SELECT pg_create_logical_replication_slot('pslot$i', 'pgoutput');" done; # 5 load data for i in $(seq 0 11); do for j in $(seq 0 10); do vsql -d pubdb$i -p 64000 -c "BEGIN; INSERT INTO t1 VALUES($j,'a'); UPDATE t1 SET c2='b' WHERE c1=$j-1; COMMIT;" & done; sleep 2 done; wait # 订阅 gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription for i in $(seq 0 11); do vsql -d postgres -p 65000 -c "CREATE DATABASE subdb$i;" vsql -d subdb$i -p 65000 -c "CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT);" vsql -d subdb$i -p 65000 -c "CREATE SUBSCRIPTION sub$i CONNECTION 'host=127.0.0.1 port=64001 dbname=pubdb$i user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub$i WITH (create_slot=false, slot_name = 'pslot$i', copy_data = false, worker_number=30);" done; sleep 5 # 6 check for i in $(seq 0 11); do vsql -d pubdb$i -p 64000 -c "SELECT count(1) FROM t1" done; for i in $(seq 0 11); do vsql -d subdb$i -p 65000 -c "SELECT count(1) FROM t1" done; vsql -d postgres -p 64000 -c " SELECT * FROM pg_get_replication_slots();" vsql -d postgres -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" # 7 cleanup DROP SUBSCRIPTION sub1; \c postgres DROP DATABASE IF EXISTS pubdb; DROP DATABASE IF EXISTS subdb; DROP USER pu1; copydata export VDATA=~/g100/rep/data vkill rm -rf ~/g100/rep/data cp -r ~/g100/rep/temp ~/g100/rep/data vstart rm -rf ~/g100/rep/data1 cp -r ~/g100/rep/temp ~/g100/rep/data1 vb_guc set -D ~/g100/rep/data1 -c "port=65000" vb_ctl start -D ~/g100/rep/data1 vsql -d postgres -p 64000 -r -- 3 create publicatioin -- pub \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables; -- CREATE PUBLICATION pub1 FOR TABLE t1,t2; CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; -- INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); -- INSERT INTO t1 (c1,c2) SELECT i,'BASE_' || i || '_' || repeat('X', 13000) FROM generate_series(1, 10000) AS i; \q -- for i in $(seq 0 1000); do vsql -d pubdb -p 64000 -c "INSERT INTO t1 VALUES($i,'a'); " ; done vsql -d postgres -p 65000 -r -- sub CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (copy_data=false, worker_number=10); -- \! for i in $(seq 20000 20010); do vsql -d pubdb -p 64000 -c "INSERT INTO t1 VALUES($i,'a'); " ; done SELECT * FROM t1; SELECT * FROM vb_parallel_apply_stat(); vsql -d pubdb -p 64000 -c "SELECT count(1) FROM t1" vsql -d subdb -p 65000 -c "SELECT count(1) FROM t1" vsql -d subdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" SELECT * FROM pg_replication_slots; INSERT INTO t1 VALUES(4,'a'),(5,'b'),(6,'c'); ddl export VDATA=~/g100/rep/data vkill rm -rf ~/g100/rep/data cp -r ~/g100/rep/temp ~/g100/rep/data vb_guc set -D ~/g100/rep/data -c "enable_ddl_logical_decode=on" vstart rm -rf ~/g100/rep/data1 cp -r ~/g100/rep/temp ~/g100/rep/data1 vb_guc set -D ~/g100/rep/data1 -c "port=65000" vb_ctl start -D ~/g100/rep/data1 vsql -d postgres -p 64000 -r -- 3 create publicatioin -- pub \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables WITH (publish='insert,update,delete,truncate', ddl='all'); -- CREATE PUBLICATION pub1 FOR TABLE t1,t2; CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; -- INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); \q vsql -d postgres -p 65000 -r -- sub CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (copy_data=true, worker_number=4); SELECT * FROM t1; \! vsql -d pubdb -p 64000 -c "TRUNCATE t1" \! sleep 1 SELECT * FROM t1; \! vsql -d pubdb -p 64000 -c "DROP TABLE t1" \! sleep 1 SELECT * FROM t1; SELECT * FROM pg_replication_slots; INSERT INTO t1 VALUES(4,'a'),(5,'b'),(6,'c'); sub-txn + ddl export VDATA=~/g100/rep/data vkill rm -rf ~/g100/rep/data cp -r ~/g100/rep/temp ~/g100/rep/data vb_guc set -D ~/g100/rep/data -c "enable_ddl_logical_decode=on" vstart rm -rf ~/g100/rep/data1 cp -r ~/g100/rep/temp ~/g100/rep/data1 vb_guc set -D ~/g100/rep/data1 -c "port=65000" vb_ctl start -D ~/g100/rep/data1 vsql -d postgres -p 64000 -r -- 3 create publicatioin -- pub \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables WITH (publish='insert,update,delete,truncate', ddl='all'); -- CREATE PUBLICATION pub1 FOR TABLE t1,t2; CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); \q vsql -d postgres -p 65000 -r -- sub CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (copy_data=true, worker_number=4); SELECT * FROM t1; \! vsql -d pubdb -p 64000 -c "TRUNCATE t1" \! sleep 1 SELECT * FROM t1; \! vsql -d pubdb -p 64000 -c "DROP TABLE t1" \! sleep 1 SELECT * FROM t1; \! vsql -d pubdb -p 64000 -c "BEGIN; CREATE TABLE t3(c1 int); SAVEPOINT sp1; INSERT INTO t3 VALUES(3); SAVEPOINT sp2; TRUNCATE t3; SAVEPOINT sp3; COMMIT;" SELECT * FROM t3; DROP TABLE t3; SELECT * FROM pg_replication_slots; INSERT INTO t1 VALUES(4,'a'),(5,'b'),(6,'c'); disable export VDATA=~/g100/rep/data vkill rm -rf ~/g100/rep/data cp -r ~/g100/rep/temp ~/g100/rep/data vstart rm -rf ~/g100/rep/data1 cp -r ~/g100/rep/temp ~/g100/rep/data1 vb_guc set -D ~/g100/rep/data1 -c "port=65000" vb_ctl start -D ~/g100/rep/data1 vsql -d postgres -p 64000 -r -- 3 create publicatioin -- pub \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables WITH (ddl='all'); -- CREATE PUBLICATION pub1 FOR TABLE t1,t2; CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); \q vsql -d postgres -p 65000 -r -- sub CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (copy_data=true, worker_number=4); SELECT * FROM t1; -- SELECT * FROM pg_replication_slots; SELECT * FROM pg_stat_subscription; ALTER SUBSCRIPTION sub1 DISABLE; \! vsql -d pubdb -p 64000 -c "INSERT INTO t1 VALUES(4,'a'),(5,'b'),(6,'c');" SELECT * FROM t1; ALTER SUBSCRIPTION sub1 ENABLE; SELECT * FROM pg_replication_slots; 基础场景 vkill vinit -- 1 config vb_guc set -D $GAUSSHOME/data -c "wal_level=logical" vb_guc set -D $GAUSSHOME/data -c "max_replication_slots=100" vb_guc set -D $GAUSSHOME/data -c "max_background_workers=100" vb_guc set -D $GAUSSHOME/data -c "max_logical_replication_workers=100" sed -i '1i host all all 0.0.0.0/0 sha256\n' $GAUSSHOME/data/pg_hba.conf echo "host replication all 0.0.0.0/0 sha256" >> $GAUSSHOME/data/pg_hba.conf -- 2 startup vstart vsql -d postgres -p 50000 -r -- 3 create publicatioin \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables; SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput'); CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; -- 4 create subscription CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription \! vsql -d subdb -p 50000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=50001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false);" -- , worker_number=30 -- 172.16.103.90 \c pubdb INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); UPDATE t1 SET c1 = 2 WHERE c2 = 'c'; INSERT INTO t1 VALUES(4,'d'); BEGIN; INSERT INTO t1 VALUES(5,'e'); UPDATE t1 SET c1 = 1 WHERE c2 = 'b'; INSERT INTO t1 VALUES(6,'f'); COMMIT; BEGIN; INSERT INTO t1 VALUES(7,'d'); UPDATE t1 SET c2 = 'a' WHERE c1 = 2; COMMIT; -- 5 load data \! for i in $(seq 0 10); do vsql -d pubdb -p 50000 -c "BEGIN; INSERT INTO t1 VALUES($i,'a'); UPDATE t1 SET c2='b' WHERE c1=$i-1; COMMIT;" > /dev/null 2>&1 & done; wait \! sleep 3 -- 5 check subscription \o pubdb.out \c pubdb SELECT * FROM t1 ORDER BY c1; \o \o subdb.out \c subdb SELECT * FROM t1 ORDER BY c1; \o \! grep -Irn "rows" pubdb.out \! grep -Irn "rows" subdb.out \! diff pubdb.out subdb.out \! cat subdb.out \! rm -f pubdb.out subdb.out -- 7 cleanup DROP SUBSCRIPTION sub1; \c postgres DROP DATABASE IF EXISTS pubdb; DROP DATABASE IF EXISTS subdb; DROP USER pu1; 空库feedback vkill rm -rf $GAUSSHOME/data* cp -r ~/w5/data $GAUSSHOME/data cp -r ~/w5/data1 $GAUSSHOME/ vb_guc set -D $GAUSSHOME/data1 -c "max_connections=4000" vb_ctl start -D $GAUSSHOME/data vb_ctl start -D $GAUSSHOME/data1 # 五、发布端（发布） vsql -d pubdb -p 64000 -c "CREATE PUBLICATION pub1 FOR all tables WITH (ddl='all');" vsql -d pubdb -p 64000 -c " SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput');" vsql -d pubdb -p 64000 -c "CREATE DATABASE db1"; vsql -d pubdb -p 64000 -c "CREATE DATABASE db2"; vsql -d db1 -p 64000 -c "CREATE PUBLICATION p1 FOR all tables WITH (ddl='all');" vsql -d db1 -p 64000 -c " SELECT pg_create_logical_replication_slot('ps1', 'pgoutput');" vsql -d db2 -p 64000 -c "CREATE PUBLICATION p2 FOR all tables WITH (ddl='all');" vsql -d db2 -p 64000 -c " SELECT pg_create_logical_replication_slot('ps2', 'pgoutput');" # 六、订阅端（订阅） gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription vsql -d pubdb -p 65000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=172.16.103.90 port=64001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=220);" vsql -d pubdb -p 65000 -c "CREATE DATABASE db1"; vsql -d pubdb -p 65000 -c "CREATE DATABASE db2"; vsql -d db1 -p 65000 -c "CREATE SUBSCRIPTION s1 CONNECTION 'host=172.16.103.90 port=64001 dbname=db1 user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION p1 WITH (create_slot=false, slot_name = 'ps1', copy_data = false, worker_number=220);" vsql -d db2 -p 65000 -c "CREATE SUBSCRIPTION s2 CONNECTION 'host=172.16.103.90 port=64001 dbname=db2 user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION p2 WITH (create_slot=false, slot_name = 'ps2', copy_data = false, worker_number=220);" vsql -d pubdb -p 64000 -c "CREATE VIEW pstat AS SELECT slot_name,active,confirmed_flush, (pg_wal_lsn_diff(confirmed_flush, '0/0') / (1024 * 1024))::int as confirmed_lsn FROM pg_get_replication_slots();" # 查看复制进度 vsql -d pubdb -p 64000 -c "SELECT * FROM pstat" # 查看速度 vsql -d pubdb -p 64000 -c "SELECT (pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / (1024 * 1024))::int as mb" vsql -d pubdb -p 65000 -c "SELECT * FROM vb_parallel_apply_stat()" vsql -d pubdb -p 64000 -c "SELECT * FROM vb_parallel_apply_stat()" 子事务 1. 这是一个基于opengauss开发的数据 2. 我已经设置了一些alias命令。全量编译vcmake。增量编译：vrecmake。启动集群：vinit, vstart。强制停止集群：vkill。 3. core文件目录：/data/ 4. 并行逻辑复制关键代码：parallel_apply.cpp 5. $GAUSSHOME也就是install目录下，所有文件都是临时生成的，随便动 vkill vinit -- 1 config vb_guc set -D $GAUSSHOME/data -c "wal_level=logical" vb_guc set -D $GAUSSHOME/data -c "max_replication_slots=100" vb_guc set -D $GAUSSHOME/data -c "max_background_workers=100" vb_guc set -D $GAUSSHOME/data -c "max_logical_replication_workers=100" # vb_guc set -D $GAUSSHOME/data -c "enable_audit=on" # vb_guc set -D $GAUSSHOME/data -c "audit_buffer_size=262144" # vb_guc set -D $GAUSSHOME/data -c "audit_directory_size=1073741824" sed -i '1i host all all 0.0.0.0/0 sha256\n' $GAUSSHOME/data/pg_hba.conf echo "host replication all 0.0.0.0/0 sha256" >> $GAUSSHOME/data/pg_hba.conf -- 2 startup vstart vsql -d postgres -p 50000 -r -- 3 create publicatioin \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables; SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput'); CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; -- 4 create subscription CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription \! vsql -d subdb -p 50000 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=50001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=20);" -- , worker_number=30 -- 172.16.103.90 \c pubdb BEGIN; INSERT INTO t1 VALUES(1,'111'); -- 假如xid=1 SAVEPOINT s1; INSERT INTO t1 VALUES(2,'222'); -- 假如xid=2 [savepoint 2, xid:1-2] SAVEPOINT s2; INSERT INTO t1 VALUES(3,'333'); -- 假如xid=3 [savepoint 3, xid:1-2-3] SAVEPOINT s3; INSERT INTO t1 VALUES(4,'444'); -- 假如xid=4 [savepoint 4, xid:1-2-3-4] SAVEPOINT s4; INSERT INTO t1 VALUES(5,'555'); -- 假如xid=5 [savepoint 5, xid:1-2-3-4-5] ROLLBACK TO SAVEPOINT s2; INSERT INTO t1 VALUES(6,'666'); -- 假如xid=6 [rollback to 3: xid:1-2] SAVEPOINT s5; INSERT INTO t1 VALUES(7,'777'); -- 假如xid=7 RELEASE SAVEPOINT s2; -- 此时，xid=6和xid=7会归并到xid=2的事务中 INSERT INTO t1 VALUES(8,'888'); -- 假如xid=2（假设上面RELEASE到s1，则xid=1，和顶层事务一致） COMMIT; -- \! sleep 3 -- 5 check subscription \c pubdb SELECT xmin,* FROM t1 ORDER BY c1; \c subdb SELECT * FROM vb_parallel_apply_stat(); SELECT xmin,* FROM t1 ORDER BY c1; -- rollback to top \c pubdb BEGIN; INSERT INTO t2 VALUES(1,'111'); -- 假如xid=1 SAVEPOINT s1; INSERT INTO t2 VALUES(2,'222'); -- 假如xid=2 SAVEPOINT s2; INSERT INTO t2 VALUES(3,'333'); -- 假如xid=3 SAVEPOINT s3; INSERT INTO t2 VALUES(4,'444'); -- 假如xid=4 SAVEPOINT s4; INSERT INTO t2 VALUES(5,'555'); -- 假如xid=5 ROLLBACK TO SAVEPOINT s2; INSERT INTO t2 VALUES(6,'666'); -- 假如xid=6 SAVEPOINT s5; INSERT INTO t2 VALUES(7,'777'); -- 假如xid=7 RELEASE SAVEPOINT s1; -- 此时，xid=6和xid=7会归并到xid=2的事务中 INSERT INTO t2 VALUES(8,'888'); -- 假如xid=2（假设上面RELEASE到s1，则xid=1，和顶层事务一致） COMMIT; -- \! sleep 3 -- 5 check subscription \c pubdb SELECT xmin,* FROM t2 ORDER BY c1; \c subdb SELECT xmin,* FROM t2 ORDER BY c1; SELECT * FROM vb_parallel_apply_stat(); -- 延时检查 CREATE TABLE t3 (c1 INT, c2 TEXT ); ALTER TABLE t3 ADD CONSTRAINT t3_c1_unique UNIQUE (c1) DEFERRABLE; -- 事务1 -- 事务2 BEGIN; INSERT INTO t3 VALUES (1, 'a'); BEGIN; SET CONSTRAINTS ALL DEFERRED; INSERT INTO t3 VALUES (1, 'a'); COMMIT; COMMIT; -- 7 cleanup DROP SUBSCRIPTION sub1; \c postgres DROP DATABASE IF EXISTS pubdb; DROP DATABASE IF EXISTS subdb; DROP USER pu1; 子事务 + 主键冲突 vkill vinit -- 1 config vb_guc set -D $GAUSSHOME/data -c "wal_level=logical" vb_guc set -D $GAUSSHOME/data -c "max_replication_slots=100" vb_guc set -D $GAUSSHOME/data -c "max_background_workers=100" vb_guc set -D $GAUSSHOME/data -c "max_logical_replication_workers=100" sed -i '1i host all all 0.0.0.0/0 sha256\n' $GAUSSHOME/data/pg_hba.conf echo "host replication all 0.0.0.0/0 sha256" >> $GAUSSHOME/data/pg_hba.conf -- 2 startup vstart vsql -d postgres -p 50000 -r -- 3 create publicatioin \c postgres CREATE DATABASE pubdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR all tables; SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput'); CREATE USER pu1 REPLICATION SYSADMIN LOGIN ENCRYPTED PASSWORD 'pu1.12345'; -- 4 create subscription CREATE DATABASE subdb; \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); \! gs_guc generate -S 'pu1.12345' -D $GAUSSHOME/bin -o subscription CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=50001 dbname=pubdb user=pu1 password=pu1.12345 sslmode=disable' PUBLICATION pub1 WITH (create_slot=false, slot_name = 'pslot1', copy_data = false, worker_number=20); INSERT INTO t1 VALUES(3,'old'); \c pubdb BEGIN; INSERT INTO t1 VALUES(1,'111'); -- 假如xid=1 SAVEPOINT s1; INSERT INTO t1 VALUES(2,'222'); -- 假如xid=2 [savepoint 2, xid:1-2] SAVEPOINT s2; INSERT INTO t1 VALUES(3,'333'); -- 假如xid=3 [savepoint 3, xid:1-2-3] SAVEPOINT s3; INSERT INTO t1 VALUES(4,'444'); -- 假如xid=4 [savepoint 4, xid:1-2-3-4] SAVEPOINT s4; COMMIT; \c subdb SELECT * FROM t1; SELECT * FROM vb_parallel_apply_stat(); -- 7 cleanup DROP SUBSCRIPTION sub1; \c postgres DROP DATABASE IF EXISTS pubdb; DROP DATABASE IF EXISTS subdb; DROP USER pu1; 子事务开发 -- 子事务开发 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); SELECT pg_current_wal_lsn(); -- 场景一：relase commit BEGIN; INSERT INTO t1 VALUES(1,'111'); -- 假如xid=1 SAVEPOINT s1; INSERT INTO t1 VALUES(2,'222'); -- 假如xid=2 SAVEPOINT s2; INSERT INTO t1 VALUES(3,'333'); -- 假如xid=3 SAVEPOINT s3; INSERT INTO t1 VALUES(4,'444'); -- 假如xid=4 SAVEPOINT s4; INSERT INTO t1 VALUES(5,'555'); -- 假如xid=5 ROLLBACK TO SAVEPOINT s2; INSERT INTO t1 VALUES(6,'666'); -- 假如xid=6 SAVEPOINT s5; INSERT INTO t1 VALUES(7,'777'); -- 假如xid=7 RELEASE SAVEPOINT s2; -- 此时，xid=6和xid=7会归并到xid=2的事务中 INSERT INTO t1 VALUES(8,'888'); -- 假如xid=2（假设上面RELEASE到s1，则xid=1，和顶层事务一致） COMMIT; SELECT xmin,* FROM t1 ORDER BY c1; \! pg_xlogdump -p $GAUSSHOME/data/pg_xlog -s xxx -x xid -- 场景二：release abort BEGIN; INSERT INTO t1 VALUES(1,'111'); -- 假如xid=1 SAVEPOINT s1; INSERT INTO t1 VALUES(2,'222'); -- 假如xid=2 SAVEPOINT s2; INSERT INTO t1 VALUES(3,'333'); -- 假如xid=3 SAVEPOINT s3; INSERT INTO t1 VALUES(4,'444'); -- 假如xid=4 SAVEPOINT s4; INSERT INTO t1 VALUES(5,'555'); -- 假如xid=5 ROLLBACK TO SAVEPOINT s2; INSERT INTO t1 VALUES(6,'666'); -- 假如xid=6 SAVEPOINT s5; INSERT INTO t1 VALUES(7,'777'); -- 假如xid=7 RELEASE SAVEPOINT s2; -- 此时，xid=6和xid=7会归并到xid=2的事务中 INSERT INTO t1 VALUES(8,'888'); -- 假如xid=2（假设上面RELEASE到s1，则xid=1，和顶层事务一致） ROLLBACK TO SAVEPOINT s1; COMMIT; SELECT xmin,* FROM t1 ORDER BY c1; -- pg 用例 echo " wal_level=logical max_worker_processes=20 max_logical_replication_workers=20 max_parallel_apply_workers_per_subscription=10 " >> $PG_HOME/data/postgresql.conf \c postgres CREATE DATABASE pubdb; CREATE DATABASE subdb; \c pubdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE PUBLICATION pub1 FOR ALL TABLES; CREATE USER pu1 REPLICATION LOGIN PASSWORD 'pu1.12345'; SELECT pg_create_logical_replication_slot('pslot1', 'pgoutput'); -- 2 create subscription \c subdb CREATE TABLE t1(c1 INT PRIMARY KEY, c2 TEXT); CREATE TABLE t2(c1 INT PRIMARY KEY, c2 TEXT); CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1 port=5432 dbname=pubdb user=pu1 password=pu1.12345 replication=database' PUBLICATION pub1 WITH ( create_slot=false, slot_name='pslot1', copy_data=false, streaming=parallel ); -- 3 test transaction -- \c pubdb -- BEGIN; -- INSERT INTO t1 VALUES(1,'111'); -- top xid -- SAVEPOINT s1; -- INSERT INTO t1 VALUES(2,'222'); -- subxid 2 -- SAVEPOINT s2; -- INSERT INTO t1 VALUES(3,'333'); -- subxid 3 -- SAVEPOINT s3; -- INSERT INTO t1 VALUES(4,'444'); -- subxid 4 -- SAVEPOINT s4; -- INSERT INTO t1 VALUES(5,'555'); -- subxid 5 -- ROLLBACK TO SAVEPOINT s2; -- INSERT INTO t1 VALUES(6,'666'); -- subxid 6 -- SAVEPOINT s5; -- INSERT INTO t1 VALUES(7,'777'); -- subxid 7 -- RELEASE SAVEPOINT s2; -- INSERT INTO t1 VALUES(8,'888'); -- 回到s1对应事务上下文 -- COMMIT; \c pubdb BEGIN; INSERT INTO t1 VALUES(1,'111'); -- 假如xid=1 SAVEPOINT s1; INSERT INTO t1 VALUES(2,'222'); -- 假如xid=2 SAVEPOINT s2; INSERT INTO t1 VALUES(3,'333'); -- 假如xid=3 SAVEPOINT s3; INSERT INTO t1 VALUES(4,'444'); -- 假如xid=4 SAVEPOINT s4; INSERT INTO t1 VALUES(5,'555'); -- 假如xid=5 ROLLBACK TO SAVEPOINT s2; INSERT INTO t1 VALUES(6,'666'); -- 假如xid=6 SAVEPOINT s5; INSERT INTO t1 VALUES(7,'777'); -- 假如xid=7 RELEASE SAVEPOINT s2; -- 此时，xid=6和xid=7会归并到xid=2的事务中 INSERT INTO t1 VALUES(8,'888'); -- 假如xid=2（假设上面RELEASE到s1，则xid=1，和顶层事务一致） ROLLBACK TO SAVEPOINT s1; COMMIT; -- 4 check \c pubdb SELECT xmin,* FROM t1 ORDER BY c1; \c subdb SELECT * FROM pg_stat_subscription; SELECT xmin,* FROM t1 ORDER BY c1; # 发布端：2阶段处理 ReleaseSavepoint for loop: cur = CurrentTransactionState cur->blockState = TBLOCK_SUBRELEASE cur = cur->parent CommitTransactionCommand if CurrentTransactionState is TBLOCK_SUBRELEASE for loop: CommitSubTransaction AtSubCommit_childXids CurrentTransactionState->parent->childXids[cnt++] = CurrentTransactionState->xid PopTransaction CurrentTransactionState = s->parent # 订阅端： pa_start_subtrans DefineSavepoint subxactlist = lappend_xid(subxactlist, current_xid) apply_handle_stream_commit pa_stream_abort for loop: curxid = subxactlist if curxid == subxid: RollbackToSavepoint 实现原理： ...

1 正常场景 CREATE USER u1 PASSWORD 'u1.password'; set role u1 password 'u1.password'; -- ddl DROP TABLE t1; DROP FUNCTION f1; CREATE TABLE t1(c1 INT, c2 TEXT); -- dml INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'); UPDATE t1 SET c2 = 'aba' WHERE c1 = 1; SELECT * FROM t1; DELETE FROM t1 WHERE c1 = 2; -- func CREATE FUNCTION f1() RETURNS INT AS $$ SELECT count(*)::INT FROM t1 $$ LANGUAGE sql; SELECT f1(); -- trans BEGIN; SELECT * FROM t1; COMMIT; DROP TABLE t1; DROP FUNCTION f1; reset role; select * from pg_audit_log; 2 异常场景 CREATE USER u1 PASSWORD 'u1.password'; CREATE TABLE st1(c1 INT, c2 TEXT); set role u1 password 'u1.password'; -- doest't exist SELECT * FROM nt1; -- permission deny SELECT * FROM st1; reset role; select * from pg_audit_log;

1 adt_scope_ddl /* ============================= Operate create alter drop truncate prepare dealocate abort begin release savepoint analyze checkpoint commit commit prepared end rollback rollback prepare prepare prepare transcation rollback to savepoint savepoint start transaction call execute close explain cluster comment copy discard do declare move fetch grant revoke import listen notify unlisten load lock reassign refresh reindex reset set set constraints set role set session authorization set transcation show values Object: system user role group database table space schema table temp table forgien table index view materialized view sequence function procedure trigger aggregate larger object domain type cast conversion collation language rule forgien data wrapper server user mapping publication subscription privileges policy statistics routine text search configuration text search dictionary text search parser text search template operator operator class operator family access method extension -- ============================= */ -- fdw \c atlasdb CREATE EXTENSION postgres_fdw; CREATE USER u1 PASSWORD 'u1.password'; CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.16.100.134', port '5432', dbname 'fdb1'); GRANT ALL ON FOREIGN SERVER s1 TO u1; set role u1; CREATE FOREIGN TABLE t1 (c1 INT) SERVER s1 OPTIONS (schema_name 'public', table_name 'ft1'); CREATE USER MAPPING FOR u1 SERVER s1 OPTIONS (user 'fu1', password 'fu1.password'); -- common CREATE TABLE tbl(c1 INT, c2 TEXT); INSERT INTO tbl VALUES(generate_series(1, 100), 'bbbbbbbbb'); -- cursor | declare,fetch,move,close | (must in transaction block) BEGIN; DECLARE cu1 CURSOR FOR SELECT * FROM tbl; -- consider it as a cache for select result FETCH 3 FROM cu1; FETCH 3 FROM cu1; MOVE FORWARD 2 FROM cu1; CLOSE cu1; COMMIT; -- channel | listen,unlisten,notify -- session 1 LISTEN ch1; -- session 2 --> session 1, session 1 receive and cache message NOTIFY ch1, 'hello'; SELECT pg_notify('ch1', 'hello' || 'a'); UNLISTEN ch1; -- table | create,alter(rename,move),drop,lock,insert,update,select,delete CREATE TABLE tb1(c1 INT); ALTER TABLE tb1 ADD COLUMN c3(INT); ALTER TABLE tb1 RENAME COLUMN c3 TO c2; BEGIN; LOCK TABLE tb1 IN ACCESS EXCLUSIVE MODE; COMMIT; DROP TABLE tb1; -- materialized view CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM tbl; -- dictionary & configuration | CREATE TEXT SEARCH DICTIONARY tsd1 ( TEMPLATE = snowball, language = english ); ALTER TEXT SEARCH DICTIONARY tsd1 (stopwords = english); CREATE TEXT SEARCH CONFIGURATION tsc1 (copy = english); ALTER TEXT SEARCH CONFIGURATION tsc1 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart WITH tsd1, simple; SELECT to_tsvector('tsc1', 'abc bb cc bb'); DROP TEXT SEARCH CONFIGURATION tsc1; DROP TEXT SEARCH DICTIONARY tsd1; -- domain | create,alter,drop CREATE DOMAIN do1 AS TEXT CONSTRAINT c1 CHECK ( VALUE ~ '^[0-9]+$' ) DEFAULT '3'; -- consider a group of 'check / not null / default' for column ALTER DOMAIN do1 ADD CONSTRAINT c2 CHECK (LEFT(VALUE, 1) < '5'); CREATE TABLE t1(c1 do1); INSERT INTO t1 VALUES ('12'); INSERT INTO t1 VALUES ('89'); INSERT INTO t1 VALUES (default); DROP TABLE t1; DROP DOMAIN do1; -- conversion | create | (need define c code) -- CREATE FUNCTION f1(integer, cstring, integer, cstring) RETURNS integer AS 'latin1_utf8', 'latin1_to_utf8' LANGUAGE C STRICT; -- CREATE CONVERSION co1 FOR 'LATIN1' TO 'UTF8' FROM f1; -- SELECT CONVERT('\xe4\xf6\xfc'::bytea, 'latin1_utf8_cnv'); -- DROP CONVERSION co1; -- DROP FUNCTION f1; -- user CREATE USER u1 PASSWORD 'u1.password'; CREATE USER u2 PASSWORD 'u2.password'; CREATE USER u3 PASSWORD 'u2.password'; ALTER USER u1 PASSWORD 'u1.newpass'; DROP USER u1; DROP USER IF EXISTS u1,u2,u3,u4; -- type CREATE TYPE int_2 AS (a INT, b INT); -- synonym (only oracle) CREATE TABLE t1 (c1 INT); CREATE SYNONYM s1 FOR t1; CREATE PUBLIC SYNONYM ps1 FOR t1; SELECT * FROM s1; SELECT * FROM ps1; DROP SYNONYM s1; DROP SYNONYM ps1; -- aggregate CREATE FUNCTION f_add_a_int(state bigint[], val int) RETURNS bigint[] LANGUAGE sql AS $$ SELECT ARRAY[state[1] + val, state[2] + 1] $$; CREATE FUNCTION f_avg_final(state bigint[]) RETURNS numeric LANGUAGE sql AS $$ SELECT state[1]::numeric / state[2] $$; CREATE AGGREGATE a_int_avg(int) ( SFUNC = f_add_a_int, STYPE = bigint[], FINALFUNC = f_avg_final, INITCOND = '{0,0}' ); SELECT a_int_avg(v) FROM generate_series(1,6) v; DROP AGGREGATE a_int_avg(int); DROP FUNCTION f_add_a_int; DROP FUNCTION f_avg_final; 2 adt_scope_dcl 3 adt_scope_dml 4 adt_scope_dql 5 adt_scope_tcl 6 adt_scope_func 7 adt_scope_mng 8 adt_scope_misc