1SELECT * FROM pglogical_regress_variables()
2\gset
3
4\c :subscriber_dsn
5GRANT ALL ON SCHEMA public TO nonsuper;
6SELECT E'\'' || current_database() || E'\'' AS subdb;
7\gset
8
9\c :provider_dsn
10
11SELECT * FROM pglogical.create_replication_set('delay');
12
13\c :subscriber_dsn
14
15CREATE or REPLACE function int2interval (x integer) returns interval as
16$$ select $1*'1 sec'::interval $$
17language sql;
18
19SELECT * FROM pglogical.create_subscription(
20    subscription_name := 'test_subscription_delay',
21    provider_dsn := (SELECT provider_dsn FROM pglogical_regress_variables()) || ' user=super',
22	replication_sets := '{delay}',
23	forward_origins := '{}',
24	synchronize_structure := false,
25	synchronize_data := false,
26	apply_delay := int2interval(2) -- 2 seconds
27);
28
29BEGIN;
30SET LOCAL statement_timeout = '30s';
31SELECT pglogical.wait_for_subscription_sync_complete('test_subscription_delay');
32COMMIT;
33
34SELECT sync_kind, sync_subid, sync_nspname, sync_relname, sync_status IN ('y', 'r') FROM pglogical.local_sync_status ORDER BY 2,3,4;
35
36SELECT status FROM pglogical.show_subscription_status() WHERE subscription_name = 'test_subscription_delay';
37
38-- Make sure we see the slot and active connection
39\c :provider_dsn
40SELECT plugin, slot_type, database, active FROM pg_replication_slots;
41SELECT count(*) FROM pg_stat_replication;
42
43CREATE TABLE public.timestamps (
44        id text primary key,
45        ts timestamptz
46);
47
48SELECT pglogical.replicate_ddl_command($$
49    CREATE TABLE public.basic_dml1 (
50        id serial primary key,
51        other integer,
52        data text,
53        something interval
54    );
55$$);
56-- clear old applies, from any previous tests etc.
57SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
58
59INSERT INTO timestamps VALUES ('ts1', CURRENT_TIMESTAMP);
60
61SELECT * FROM pglogical.replication_set_add_table('delay', 'basic_dml1');
62
63SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
64
65INSERT INTO timestamps VALUES ('ts2', CURRENT_TIMESTAMP);
66
67INSERT INTO basic_dml1(other, data, something)
68VALUES (5, 'foo', '1 minute'::interval),
69       (4, 'bar', '12 weeks'::interval),
70       (3, 'baz', '2 years 1 hour'::interval),
71       (2, 'qux', '8 months 2 days'::interval),
72       (1, NULL, NULL);
73
74SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL);
75
76INSERT INTO timestamps VALUES ('ts3', CURRENT_TIMESTAMP);
77
78SELECT round (EXTRACT(EPOCH FROM (SELECT ts from timestamps where id = 'ts2')) -
79       EXTRACT(EPOCH FROM (SELECT ts from timestamps where id = 'ts1'))) :: integer >= 2 as ddl_replication_delayed;
80SELECT round (EXTRACT(EPOCH FROM (SELECT ts from timestamps where id = 'ts3')) -
81       EXTRACT(EPOCH FROM (SELECT ts from timestamps where id = 'ts2'))) :: integer >= 2 as inserts_replication_delayed;
82
83\c :subscriber_dsn
84
85SELECT * FROM basic_dml1;
86
87SELECT pglogical.drop_subscription('test_subscription_delay');
88
89\c :provider_dsn
90\set VERBOSITY terse
91SELECT * FROM pglogical.drop_replication_set('delay');
92DROP TABLE public.timestamps CASCADE;
93SELECT pglogical.replicate_ddl_command($$
94    DROP TABLE public.basic_dml1 CASCADE;
95$$);
96