1-- row based filtering 2SELECT * FROM pglogical_regress_variables() 3\gset 4 5\c :provider_dsn 6-- testing volatile sampling function in row_filter 7SELECT pglogical.replicate_ddl_command($$ 8 CREATE TABLE public.test_tablesample (id int primary key, name text) WITH (fillfactor=10); 9$$); 10-- use fillfactor so we don't have to load too much data to get multiple pages 11INSERT INTO test_tablesample 12 SELECT i, repeat(i::text, 200) FROM generate_series(0, 9) s(i); 13 14create or replace function funcn_get_system_sample_count(integer, integer) returns bigint as 15$$ (SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM ($1) REPEATABLE ($2)); $$ 16language sql volatile; 17 18create or replace function funcn_get_bernoulli_sample_count(integer, integer) returns bigint as 19$$ (SELECT count(*) FROM test_tablesample TABLESAMPLE BERNOULLI ($1) REPEATABLE ($2)); $$ 20language sql volatile; 21 22SELECT * FROM pglogical.replication_set_add_table('default', 'test_tablesample', false, row_filter := $rf$id > funcn_get_system_sample_count(100, 3) $rf$); 23SELECT * FROM pglogical.replication_set_remove_table('default', 'test_tablesample'); 24SELECT * FROM pglogical.replication_set_add_table('default', 'test_tablesample', true, row_filter := $rf$id > funcn_get_bernoulli_sample_count(10, 0) $rf$); 25 26SELECT * FROM test_tablesample ORDER BY id limit 5; 27SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 28 29\c :subscriber_dsn 30 31BEGIN; 32SET LOCAL statement_timeout = '10s'; 33SELECT pglogical.wait_for_table_sync_complete('test_subscription', 'test_tablesample'); 34COMMIT; 35 36SELECT sync_kind, sync_nspname, sync_relname, sync_status FROM pglogical.local_sync_status WHERE sync_relname = 'test_tablesample'; 37 38SELECT * FROM test_tablesample ORDER BY id limit 5; 39 40\c :provider_dsn 41\set VERBOSITY terse 42DROP FUNCTION funcn_get_system_sample_count(integer, integer); 43DROP FUNCTION funcn_get_bernoulli_sample_count(integer, integer); 44SELECT pglogical.replicate_ddl_command($$ 45 DROP TABLE public.test_tablesample CASCADE; 46$$); 47