File: powa--4.1.1--4.1.2.sql

package info (click to toggle)
powa-archivist 4.1.2-1
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, sid
  • size: 1,184 kB
  • sloc: sql: 18,289; ansic: 435; makefile: 34; sh: 4
file content (252 lines) | stat: -rw-r--r-- 9,982 bytes parent folder | download
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 */