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
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
|
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
--\echo Use "ALTER EXTENSION powa" to load this file. \quit
ALTER TABLE public.powa_extensions DROP CONSTRAINT powa_extensions_srvid_fkey;
ALTER TABLE public.powa_extensions ADD
FOREIGN KEY (srvid) REFERENCES public.powa_servers (id)
MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
CREATE OR REPLACE FUNCTION powa_qualstats_snapshot(_srvid integer) RETURNS void as $PROC$
DECLARE
result bool;
v_funcname text := 'powa_qualstats_snapshot';
v_rowcount bigint;
BEGIN
PERFORM powa_log(format('running %I', v_funcname));
PERFORM powa_prevent_concurrent_snapshot(_srvid);
WITH capture AS (
SELECT *
FROM powa_qualstats_src(_srvid) q
WHERE EXISTS (SELECT 1
FROM powa_statements s
WHERE s.srvid = _srvid
AND q.queryid = s.queryid
AND q.dbid = s.dbid
AND q.userid = s.userid)
),
missing_quals AS (
INSERT INTO public.powa_qualstats_quals (srvid, qualid, queryid, dbid, userid, quals)
SELECT DISTINCT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
array_agg(DISTINCT q::qual_type)
FROM capture qs,
LATERAL (SELECT (unnest(quals)).*) as q
WHERE NOT EXISTS (
SELECT 1
FROM powa_qualstats_quals nh
WHERE nh.srvid = _srvid
AND nh.qualid = qs.qualnodeid
AND nh.queryid = qs.queryid
AND nh.dbid = qs.dbid
AND nh.userid = qs.userid
)
GROUP BY srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual AS (
INSERT INTO public.powa_qualstats_quals_history_current (srvid, qualid, queryid,
dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num)
SELECT _srvid AS srvid, qs.qualnodeid, qs.queryid, qs.dbid, qs.userid,
ts, sum(occurences), sum(execution_count), sum(nbfiltered),
avg(mean_err_estimate_ratio), avg(mean_err_estimate_num)
FROM capture as qs
GROUP BY srvid, ts, qualnodeid, qs.queryid, qs.dbid, qs.userid
RETURNING *
),
by_qual_with_const AS (
INSERT INTO public.powa_qualstats_constvalues_history_current(srvid, qualid,
queryid, dbid, userid, ts, occurences, execution_count, nbfiltered,
mean_err_estimate_ratio, mean_err_estimate_num, constvalues)
SELECT _srvid, qualnodeid, qs.queryid, qs.dbid, qs.userid, ts,
occurences, execution_count, nbfiltered, mean_err_estimate_ratio,
mean_err_estimate_num, constvalues
FROM capture as qs
)
SELECT COUNT(*) into v_rowcount
FROM capture;
perform powa_log(format('%I - rowcount: %s',
v_funcname, v_rowcount));
IF (_srvid != 0) THEN
DELETE FROM powa_qualstats_src_tmp WHERE srvid = _srvid;
END IF;
result := true;
-- pg_qualstats metrics are not accumulated, so we force a reset after every
-- snapshot. For local snapshot this is done here, remote snapshots will
-- rely on the collector doing it through query_cleanup.
IF (_srvid = 0) THEN
PERFORM pg_qualstats_reset();
END IF;
END
$PROC$ language plpgsql; /* end of powa_qualstats_snapshot */
CREATE OR REPLACE FUNCTION public.powa_statements_reset(_srvid integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM public.powa_log('Resetting powa_statements_history(' || _srvid || ')');
DELETE FROM public.powa_statements_history WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_statements_history_current(' || _srvid || ')');
DELETE FROM public.powa_statements_history_current WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_statements_history_db(' || _srvid || ')');
DELETE FROM public.powa_statements_history_db WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_statements_history_current_db(' || _srvid || ')');
DELETE FROM public.powa_statements_history_current_db WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_statements_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_statements_src_tmp WHERE srvid = _srvid;
-- if 3rd part datasource has FK on it, throw everything away
DELETE FROM public.powa_statements WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_statements(' || _srvid || ')');
RETURN true;
END;
$function$; /* end of powa_statements_reset */
CREATE OR REPLACE FUNCTION public.powa_user_functions_reset(_srvid integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM public.powa_log('Resetting powa_user_functions_history(' || _srvid || ')');
DELETE FROM public.powa_user_functions_history WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_user_functions_history_current(' || _srvid || ')');
DELETE FROM public.powa_user_functions_history_current WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_user_functions_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_user_functions_src_tmp WHERE srvid = _srvid;
RETURN true;
END;
$function$; /* end of powa_user_functions_reset */
CREATE OR REPLACE FUNCTION public.powa_all_relations_reset(_srvid integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM public.powa_log('Resetting powa_all_relations_history(' || _srvid || ')');
DELETE FROM public.powa_all_relations_history WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_all_relations_history_db(' || _srvid || ')');
DELETE FROM public.powa_all_relations_history_db WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_all_relations_history_current(' || _srvid || ')');
DELETE FROM public.powa_all_relations_history_current WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_all_relations_history_current_db(' || _srvid || ')');
DELETE FROM public.powa_all_relations_history_current_db WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_all_relations_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_all_relations_src_tmp WHERE srvid = _srvid;
RETURN true;
END;
$function$; /* end of powa_all_relations_reset */
CREATE OR REPLACE FUNCTION public.powa_stat_bgwriter_reset(_srvid integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM public.powa_log('Resetting powa_stat_bgwriter_history(' || _srvid || ')');
DELETE FROM public.powa_stat_bgwriter_history WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_stat_bgwriter_history_current(' || _srvid || ')');
DELETE FROM public.powa_stat_bgwriter_history_current WHERE srvid = _srvid;
PERFORM public.powa_log('Resetting powa_stat_bgwriter_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_stat_bgwriter_src_tmp WHERE srvid = _srvid;
RETURN true;
END;
$function$; /* end of powa_stat_bgwriter_reset */
/*
* powa_kcache reset
*/
CREATE OR REPLACE FUNCTION powa_kcache_reset(_srvid integer)
RETURNS void as $PROC$
DECLARE
v_funcname text := 'powa_kcache_reset(' || _srvid || ')';
v_rowcount bigint;
BEGIN
PERFORM public.powa_log(format('running %I', v_funcname));
PERFORM public.powa_log('resetting powa_kcache_metrics(' || _srvid || ')');
DELETE FROM public.powa_kcache_metrics WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_kcache_metrics_db(' || _srvid || ')');
DELETE FROM public.powa_kcache_metrics_db WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_kcache_metrics_current(' || _srvid || ')');
DELETE FROM public.powa_kcache_metrics_current WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_kcache_metrics_current_db(' || _srvid || ')');
DELETE FROM public.powa_kcache_metrics_current_db WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_kcache_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_kcache_src_tmp WHERE srvid = _srvid;
END;
$PROC$ language plpgsql; /* end of powa_kcache_reset */
/*
* powa_qualstats_reset
*/
CREATE OR REPLACE FUNCTION powa_qualstats_reset(_srvid integer)
RETURNS void as $PROC$
BEGIN
PERFORM public.powa_log('running powa_qualstats_reset(' || _srvid || ')');
PERFORM public.powa_log('resetting powa_qualstats_quals(' || _srvid || ')');
DELETE FROM public.powa_qualstats_quals WHERE srvid = _srvid;
-- cascaded :
-- powa_qualstats_quals_history
-- powa_qualstats_quals_history_current
-- powa_qualstats_constvalues_history
-- powa_qualstats_constvalues_history_current
PERFORM public.powa_log('resetting powa_qualstats_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_qualstats_src_tmp WHERE srvid = _srvid;
END;
$PROC$ language plpgsql; /* end of powa_qualstats_reset */
/*
* powa_wait_sampling reset
*/
CREATE OR REPLACE FUNCTION powa_wait_sampling_reset(_srvid integer)
RETURNS void as $PROC$
DECLARE
v_funcname text := 'powa_wait_sampling_reset(' || _srvid || ')';
v_rowcount bigint;
BEGIN
PERFORM public.powa_log(format('running %I', v_funcname));
PERFORM public.powa_log('resetting powa_wait_sampling_history(' || _srvid || ')');
DELETE FROM public.powa_wait_sampling_history WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_wait_sampling_history_db(' || _srvid || ')');
DELETE FROM public.powa_wait_sampling_history_db WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_wait_sampling_history_current(' || _srvid || ')');
DELETE FROM public.powa_wait_sampling_history_current WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_wait_sampling_history_current_db(' || _srvid || ')');
DELETE FROM public.powa_wait_sampling_history_current_db WHERE srvid = _srvid;
PERFORM public.powa_log('resetting powa_wait_sampling_src_tmp(' || _srvid || ')');
DELETE FROM public.powa_wait_sampling_src_tmp WHERE srvid = _srvid;
END;
$PROC$ language plpgsql; /* end of powa_wait_sampling_reset */
|
