1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
-- Suppress pid-specific warning messages
SET client_min_messages TO error;
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, lock_safe_deployment, allow_multi_statements)
VALUES ('test1','.*',true, true);
-- It's generally good to use queue_subscriber_failures with include_everything, so a bogus grant won't break replication on subscriber
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_everything, queue_subscriber_failures, create_tags)
VALUES ('test1',true, true, '{GRANT,REVOKE}');
SELECT pgl_ddl_deploy.deploy(id) FROM pgl_ddl_deploy.set_configs WHERE set_name = 'test1';
DISCARD TEMP;
SET search_path TO public;
SET ROLE test_pgl_ddl_deploy;
CREATE TABLE foo(id serial primary key, bla int);
SELECT set_name, ddl_sql_raw, ddl_sql_sent FROM pgl_ddl_deploy.events ORDER BY id DESC LIMIT 10;
GRANT SELECT ON foo TO PUBLIC;
SELECT c.set_name, ddl_sql_raw, ddl_sql_sent, c.include_everything
FROM pgl_ddl_deploy.events e
INNER JOIN pgl_ddl_deploy.set_configs c ON c.id = e.set_config_id
ORDER BY e.id DESC LIMIT 10;
INSERT INTO foo (bla) VALUES (1),(2),(3);
REVOKE INSERT ON foo FROM PUBLIC;
DROP TABLE foo CASCADE;
SELECT c.set_name, ddl_sql_raw, ddl_sql_sent, c.include_everything
FROM pgl_ddl_deploy.events e
INNER JOIN pgl_ddl_deploy.set_configs c ON c.id = e.set_config_id
ORDER BY e.id DESC LIMIT 10;
SELECT * FROM pgl_ddl_deploy.unhandled;
SELECT * FROM pgl_ddl_deploy.exceptions;
/*****
Test cancel and terminate blocker functionality
*****/
SET ROLE postgres;
UPDATE pgl_ddl_deploy.set_configs SET lock_safe_deployment = FALSE, signal_blocking_subscriber_sessions = 'cancel';
SELECT pgl_ddl_deploy.deploy(id) FROM pgl_ddl_deploy.set_configs WHERE set_name = 'test1';
SET ROLE test_pgl_ddl_deploy;
CREATE TABLE foo(id serial primary key, bla int);
SELECT set_name, ddl_sql_raw, ddl_sql_sent FROM pgl_ddl_deploy.events ORDER BY id DESC LIMIT 10;
GRANT SELECT ON foo TO PUBLIC;
SELECT c.set_name, ddl_sql_raw, ddl_sql_sent, c.include_everything
FROM pgl_ddl_deploy.events e
INNER JOIN pgl_ddl_deploy.set_configs c ON c.id = e.set_config_id
ORDER BY e.id DESC LIMIT 10;
INSERT INTO foo (bla) VALUES (1),(2),(3);
REVOKE INSERT ON foo FROM PUBLIC;
DROP TABLE foo CASCADE;
SELECT c.set_name, ddl_sql_raw, ddl_sql_sent, c.include_everything
FROM pgl_ddl_deploy.events e
INNER JOIN pgl_ddl_deploy.set_configs c ON c.id = e.set_config_id
ORDER BY e.id DESC LIMIT 10;
SELECT * FROM pgl_ddl_deploy.unhandled;
SELECT * FROM pgl_ddl_deploy.exceptions;
CREATE TABLE public.foo(id serial primary key, bla int);
CREATE TABLE public.foo2 () INHERITS (public.foo);
CREATE TABLE public.bar(id serial primary key, bla int);
\! PGOPTIONS='--client-min-messages=warning' psql -d contrib_regression -c "BEGIN; SELECT * FROM public.foo; SELECT pg_sleep(30);" > /dev/null 2>&1 &
SELECT pg_sleep(1);
SELECT signal, successful, state, query, reported, pg_sleep(1)
FROM pgl_ddl_deploy.kill_blockers('cancel','public','foo');
\! PGOPTIONS='--client-min-messages=warning' psql -d contrib_regression -c "BEGIN; SELECT * FROM public.foo; SELECT pg_sleep(30);" > /dev/null 2>&1 &
SELECT pg_sleep(1);
SELECT signal, successful, state, query, reported, pg_sleep(1)
FROM pgl_ddl_deploy.kill_blockers('terminate','public','foo');
\! PGOPTIONS='--client-min-messages=warning' psql -d contrib_regression -c "BEGIN; SELECT * FROM public.foo; SELECT pg_sleep(30);" > /dev/null 2>&1 &
-- This process should not be killed
\! PGOPTIONS='--client-min-messages=warning' psql -d contrib_regression -c "BEGIN; INSERT INTO public.bar (bla) VALUES (1); SELECT pg_sleep(2); COMMIT;" > /dev/null 2>&1 &
SELECT pg_sleep(1);
SELECT pgl_ddl_deploy.subscriber_command
(
p_provider_name := 'test',
p_set_name := ARRAY['test1'],
p_nspname := 'public',
p_relname := 'foo',
p_ddl_sql_sent := $pgl_ddl_deploy_sql$ALTER TABLE public.foo ADD COLUMN bar text;$pgl_ddl_deploy_sql$,
p_full_ddl := $pgl_ddl_deploy_sql$
--Be sure to use provider's search_path for SQL environment consistency
SET SEARCH_PATH TO public;
ALTER TABLE public.foo ADD COLUMN bar text;
;
$pgl_ddl_deploy_sql$,
p_pid := pg_backend_pid(),
p_set_config_id := 1,
p_queue_subscriber_failures := false,
p_signal_blocking_subscriber_sessions := 'cancel',
-- Lower lock_timeout to make this test run faster
p_lock_timeout := 300,
p_driver := (SELECT driver FROM pgl_ddl_deploy.rep_set_wrapper() WHERE name = 'test1'),
-- This parameter is only marked TRUE for this function to be able to easily run on a provider for regression testing
p_run_anywhere := TRUE
);
TABLE public.foo;
-- Now two processes to be killed
\! PGOPTIONS='--client-min-messages=warning' psql -d contrib_regression -c "BEGIN; SELECT * FROM public.foo; SELECT pg_sleep(30);" > /dev/null 2>&1 &
SELECT pg_sleep(1);
-- This process will wait for the one above - but we want it to fail regardless of which gets killed first
-- Avoid it firing our event triggers by using session_replication_role = replica
\! PGOPTIONS='--client-min-messages=warning --session-replication-role=replica' psql -d contrib_regression -c "BEGIN; ALTER TABLE public.foo DROP COLUMN bar; SELECT pg_sleep(30);" > /dev/null 2>&1 &
SELECT pg_sleep(2);
SELECT pgl_ddl_deploy.subscriber_command
(
p_provider_name := 'test',
p_set_name := ARRAY['test1'],
p_nspname := 'public',
p_relname := 'foo',
p_ddl_sql_sent := $pgl_ddl_deploy_sql$ALTER TABLE public.foo ADD COLUMN super text;$pgl_ddl_deploy_sql$,
p_full_ddl := $pgl_ddl_deploy_sql$
--Be sure to use provider's search_path for SQL environment consistency
SET SEARCH_PATH TO public;
ALTER TABLE public.foo ADD COLUMN super text;
;
$pgl_ddl_deploy_sql$,
p_pid := pg_backend_pid(),
p_set_config_id := 1,
p_queue_subscriber_failures := false,
p_signal_blocking_subscriber_sessions := 'terminate',
-- Lower lock_timeout to make this test run faster
p_lock_timeout := 300,
p_driver := (SELECT driver FROM pgl_ddl_deploy.rep_set_wrapper() WHERE name = 'test1'),
-- This parameter is only marked TRUE for this function to be able to easily run on a provider for regression testing
p_run_anywhere := TRUE
);
TABLE public.foo;
/****
Try cancel_then_terminate, which should first try to cancel
****/
-- This process should be killed
\! echo "BEGIN; SELECT * FROM public.foo;\n\! sleep 15" | psql contrib_regression > /dev/null 2>&1 &
-- This process should not be killed
\! psql contrib_regression -c "BEGIN; INSERT INTO public.bar (bla) VALUES (1); SELECT pg_sleep(5); COMMIT;" > /dev/null 2>&1 &
SELECT pg_sleep(1);
SELECT pgl_ddl_deploy.subscriber_command
(
p_provider_name := 'test',
p_set_name := ARRAY['test1'],
p_nspname := 'public',
p_relname := 'foo',
p_ddl_sql_sent := $pgl_ddl_deploy_sql$ALTER TABLE public.foo ALTER COLUMN bar SET NOT NULL;$pgl_ddl_deploy_sql$,
p_full_ddl := $pgl_ddl_deploy_sql$
--Be sure to use provider's search_path for SQL environment consistency
SET SEARCH_PATH TO public;
ALTER TABLE public.foo ALTER COLUMN bar SET NOT NULL;
;
$pgl_ddl_deploy_sql$,
p_pid := pg_backend_pid(),
p_set_config_id := 1,
p_queue_subscriber_failures := false,
p_signal_blocking_subscriber_sessions := 'cancel_then_terminate',
-- Lower lock_timeout to make this test run faster
p_lock_timeout := 300,
p_driver := (SELECT driver FROM pgl_ddl_deploy.rep_set_wrapper() WHERE name = 'test1'),
-- This parameter is only marked TRUE for this function to be able to easily run on a provider for regression testing
p_run_anywhere := TRUE
);
TABLE public.foo;
/*** TEST INHERITANCE AND PARTITIONING ***/
-- Same workflow as above, but instead select from child, alter parent
\! PGOPTIONS='--client-min-messages=warning' psql -d contrib_regression -c "BEGIN; SELECT * FROM public.foo2; SELECT pg_sleep(30);" > /dev/null 2>&1 &
SELECT pg_sleep(1);
SELECT signal, successful, state, query, reported, pg_sleep(1)
FROM pgl_ddl_deploy.kill_blockers('terminate','public','foo');
/*** With <=1.5, it showed this. But it should kill the process.
signal | successful | state | query | reported | pg_sleep
--------+------------+-------+-------+----------+----------
(0 rows)
***/
DROP TABLE public.foo CASCADE;
TABLE bar;
DROP TABLE public.bar CASCADE;
SELECT signal, successful, state, query, reported
FROM pgl_ddl_deploy.killed_blockers
ORDER BY signal, query;
SELECT pg_sleep(1);
-- Should be zero - everything was killed
SELECT COUNT(1)
FROM pg_stat_activity
WHERE usename = session_user
AND NOT pid = pg_backend_pid()
AND query LIKE '%public.foo%';
|
