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