postgresql: fix last sum() related parse errors
[collectd.git] / src / postgresql_default.conf
1 # Pre-defined queries of collectd's postgresql plugin.
2 #
3 # Do not edit this file. If you want to change any of the query definitions,
4 # overwrite them in collectd.conf instead.
5 #
6 # This file is distributed under the same terms as collectd itself.
7
8 <Query backends>
9         Statement "SELECT count(*) AS count \
10                 FROM pg_stat_activity \
11                 WHERE datname = $1;"
12
13         Param database
14
15         <Result>
16                 Type "pg_numbackends"
17                 ValuesFrom "count"
18         </Result>
19 </Query>
20
21 <Query transactions>
22         Statement "SELECT xact_commit, xact_rollback \
23                 FROM pg_stat_database \
24                 WHERE datname = $1;"
25
26         Param database
27
28         <Result>
29                 Type "pg_xact"
30                 InstancePrefix "commit"
31                 ValuesFrom "xact_commit"
32         </Result>
33         <Result>
34                 Type "pg_xact"
35                 InstancePrefix "rollback"
36                 ValuesFrom "xact_rollback"
37         </Result>
38 </Query>
39
40 <Query queries>
41         Statement "SELECT coalesce(sum(n_tup_ins), 0) AS ins, \
42                         coalesce(sum(n_tup_upd), 0) AS upd, \
43                         coalesce(sum(n_tup_del), 0) AS del \
44                 FROM pg_stat_user_tables;"
45
46         <Result>
47                 Type "pg_n_tup_c"
48                 InstancePrefix "ins"
49                 ValuesFrom "ins"
50         </Result>
51         <Result>
52                 Type "pg_n_tup_c"
53                 InstancePrefix "upd"
54                 ValuesFrom "upd"
55         </Result>
56         <Result>
57                 Type "pg_n_tup_c"
58                 InstancePrefix "del"
59                 ValuesFrom "del"
60         </Result>
61
62         MaxVersion 80299
63 </Query>
64
65 <Query queries>
66         Statement "SELECT coalesce(sum(n_tup_ins), 0) AS ins, \
67                         coalesce(sum(n_tup_upd), 0) AS upd, \
68                         coalesce(sum(n_tup_del), 0) AS del, \
69                         coalesce(sum(n_tup_hot_upd), 0) AS hot_upd \
70                 FROM pg_stat_user_tables;"
71
72         <Result>
73                 Type "pg_n_tup_c"
74                 InstancePrefix "ins"
75                 ValuesFrom "ins"
76         </Result>
77         <Result>
78                 Type "pg_n_tup_c"
79                 InstancePrefix "upd"
80                 ValuesFrom "upd"
81         </Result>
82         <Result>
83                 Type "pg_n_tup_c"
84                 InstancePrefix "del"
85                 ValuesFrom "del"
86         </Result>
87         <Result>
88                 Type "pg_n_tup_c"
89                 InstancePrefix "hot_upd"
90                 ValuesFrom "hot_upd"
91         </Result>
92
93         MinVersion 80300
94 </Query>
95
96 <Query queries_by_table>
97         Statement "SELECT schemaname, relname, \
98                         n_tup_ins AS ins, \
99                         n_tup_upd AS upd, \
100                         n_tup_del AS del \
101                 FROM pg_stat_user_tables;"
102
103         <Result>
104                 Type "pg_n_tup_c"
105                 InstancePrefix "ins"
106                 InstancesFrom "schemaname" "relname"
107                 ValuesFrom "ins"
108         </Result>
109         <Result>
110                 Type "pg_n_tup_c"
111                 InstancePrefix "upd"
112                 InstancesFrom "schemaname" "relname"
113                 ValuesFrom "upd"
114         </Result>
115         <Result>
116                 Type "pg_n_tup_c"
117                 InstancePrefix "del"
118                 InstancesFrom "schemaname" "relname"
119                 ValuesFrom "del"
120         </Result>
121
122         MaxVersion 80299
123 </Query>
124
125 <Query queries_by_table>
126         Statement "SELECT schemaname, relname, \
127                         n_tup_ins AS ins, \
128                         n_tup_upd AS upd, \
129                         n_tup_del AS del, \
130                         n_tup_hot_upd AS hot_upd \
131                 FROM pg_stat_user_tables;"
132
133         <Result>
134                 Type "pg_n_tup_c"
135                 InstancePrefix "ins"
136                 InstancesFrom "schemaname" "relname"
137                 ValuesFrom "ins"
138         </Result>
139         <Result>
140                 Type "pg_n_tup_c"
141                 InstancePrefix "upd"
142                 InstancesFrom "schemaname" "relname"
143                 ValuesFrom "upd"
144         </Result>
145         <Result>
146                 Type "pg_n_tup_c"
147                 InstancePrefix "del"
148                 InstancesFrom "schemaname" "relname"
149                 ValuesFrom "del"
150         </Result>
151         <Result>
152                 Type "pg_n_tup_c"
153                 InstancePrefix "hot_upd"
154                 InstancesFrom "schemaname" "relname"
155                 ValuesFrom "hot_upd"
156         </Result>
157
158         MinVersion 80300
159 </Query>
160
161 <Query query_plans>
162         Statement "SELECT coalesce(sum(seq_scan), 0) AS seq, \
163                           coalesce(sum(seq_tup_read), 0) AS seq_tup_read, \
164                           coalesce(sum(idx_scan), 0) AS idx, \
165                           coalesce(sum(idx_tup_fetch), 0) AS idx_tup_fetch \
166                 FROM pg_stat_user_tables;"
167
168         <Result>
169                 Type "pg_scan"
170                 InstancePrefix "seq"
171                 ValuesFrom "seq"
172         </Result>
173         <Result>
174                 Type "pg_scan"
175                 InstancePrefix "seq_tup_read"
176                 ValuesFrom "seq_tup_read"
177         </Result>
178         <Result>
179                 Type "pg_scan"
180                 InstancePrefix "idx"
181                 ValuesFrom "idx"
182         </Result>
183         <Result>
184                 Type "pg_scan"
185                 InstancePrefix "idx_tup_fetch"
186                 ValuesFrom "idx_tup_fetch"
187         </Result>
188 </Query>
189
190 <Query table_states>
191         Statement "SELECT coalesce(sum(n_live_tup), 0) AS live, \
192                 coalesce(sum(n_dead_tup), 0) AS dead \
193                 FROM pg_stat_user_tables;"
194
195         <Result>
196                 Type "pg_n_tup_g"
197                 InstancePrefix "live"
198                 ValuesFrom "live"
199         </Result>
200         <Result>
201                 Type "pg_n_tup_g"
202                 InstancePrefix "dead"
203                 ValuesFrom "dead"
204         </Result>
205
206         MinVersion 80300
207 </Query>
208
209 <Query query_plans_by_table>
210         Statement "SELECT schemaname, relname, \
211                         coalesce(seq_scan, 0) AS seq, \
212                         coalesce(seq_tup_read, 0) AS seq_tup_read, \
213                         coalesce(idx_scan, 0) AS idx, \
214                         coalesce(idx_tup_fetch, 0) AS idx_tup_fetch \
215                 FROM pg_stat_user_tables;"
216
217         <Result>
218                 Type "pg_scan"
219                 InstancePrefix "seq"
220                 InstancesFrom "schemaname" "relname"
221                 ValuesFrom "seq"
222         </Result>
223         <Result>
224                 Type "pg_scan"
225                 InstancePrefix "seq_tup_read"
226                 InstancesFrom "schemaname" "relname"
227                 ValuesFrom "seq_tup_read"
228         </Result>
229         <Result>
230                 Type "pg_scan"
231                 InstancePrefix "idx"
232                 InstancesFrom "schemaname" "relname"
233                 ValuesFrom "idx"
234         </Result>
235         <Result>
236                 Type "pg_scan"
237                 InstancePrefix "idx_tup_fetch"
238                 InstancesFrom "schemaname" "relname"
239                 ValuesFrom "idx_tup_fetch"
240         </Result>
241 </Query>
242
243 <Query table_states_by_table>
244         Statement "SELECT schemaname, relname, \
245                         n_live_tup AS live, n_dead_tup AS dead \
246                 FROM pg_stat_user_tables;"
247
248         <Result>
249                 Type "pg_n_tup_g"
250                 InstancePrefix "live"
251                 InstancesFrom "schemaname" "relname"
252                 ValuesFrom "live"
253         </Result>
254         <Result>
255                 Type "pg_n_tup_g"
256                 InstancePrefix "dead"
257                 InstancesFrom "schemaname" "relname"
258                 ValuesFrom "dead"
259         </Result>
260
261         MinVersion 80300
262 </Query>
263
264 <Query disk_io>
265         Statement "SELECT coalesce(sum(heap_blks_read), 0) AS heap_read, \
266                         coalesce(sum(heap_blks_hit), 0) AS heap_hit, \
267                         coalesce(sum(idx_blks_read), 0) AS idx_read, \
268                         coalesce(sum(idx_blks_hit), 0) AS idx_hit, \
269                         coalesce(sum(toast_blks_read), 0) AS toast_read, \
270                         coalesce(sum(toast_blks_hit), 0) AS toast_hit, \
271                         coalesce(sum(tidx_blks_read), 0) AS tidx_read, \
272                         coalesce(sum(tidx_blks_hit), 0) AS tidx_hit \
273                 FROM pg_statio_user_tables;"
274
275         <Result>
276                 Type "pg_blks"
277                 InstancePrefix "heap_read"
278                 ValuesFrom "heap_read"
279         </Result>
280         <Result>
281                 Type "pg_blks"
282                 InstancePrefix "heap_hit"
283                 ValuesFrom "heap_hit"
284         </Result>
285         <Result>
286                 Type "pg_blks"
287                 InstancePrefix "idx_read"
288                 ValuesFrom "idx_read"
289         </Result>
290         <Result>
291                 Type "pg_blks"
292                 InstancePrefix "idx_hit"
293                 ValuesFrom "idx_hit"
294         </Result>
295         <Result>
296                 Type "pg_blks"
297                 InstancePrefix "toast_read"
298                 ValuesFrom "toast_read"
299         </Result>
300         <Result>
301                 Type "pg_blks"
302                 InstancePrefix "toast_hit"
303                 ValuesFrom "toast_hit"
304         </Result>
305         <Result>
306                 Type "pg_blks"
307                 InstancePrefix "tidx_read"
308                 ValuesFrom "tidx_read"
309         </Result>
310         <Result>
311                 Type "pg_blks"
312                 InstancePrefix "tidx_hit"
313                 ValuesFrom "tidx_hit"
314         </Result>
315 </Query>
316
317 <Query disk_io_by_table>
318         Statement "SELECT schemaname, relname, \
319                         coalesce(heap_blks_read, 0) AS heap_read, \
320                         coalesce(heap_blks_hit, 0) AS heap_hit, \
321                         coalesce(idx_blks_read, 0) AS idx_read, \
322                         coalesce(idx_blks_hit, 0) AS idx_hit, \
323                         coalesce(toast_blks_read, 0) AS toast_read, \
324                         coalesce(toast_blks_hit, 0) AS toast_hit, \
325                         coalesce(tidx_blks_read, 0) AS tidx_read, \
326                         coalesce(tidx_blks_hit, 0) AS tidx_hit \
327                 FROM pg_statio_user_tables;"
328
329         <Result>
330                 Type "pg_blks"
331                 InstancePrefix "heap_read"
332                 InstancesFrom "schemaname" "relname"
333                 ValuesFrom "heap_read"
334         </Result>
335         <Result>
336                 Type "pg_blks"
337                 InstancePrefix "heap_hit"
338                 InstancesFrom "schemaname" "relname"
339                 ValuesFrom "heap_hit"
340         </Result>
341         <Result>
342                 Type "pg_blks"
343                 InstancePrefix "idx_read"
344                 InstancesFrom "schemaname" "relname"
345                 ValuesFrom "idx_read"
346         </Result>
347         <Result>
348                 Type "pg_blks"
349                 InstancePrefix "idx_hit"
350                 InstancesFrom "schemaname" "relname"
351                 ValuesFrom "idx_hit"
352         </Result>
353         <Result>
354                 Type "pg_blks"
355                 InstancePrefix "toast_read"
356                 InstancesFrom "schemaname" "relname"
357                 ValuesFrom "toast_read"
358         </Result>
359         <Result>
360                 Type "pg_blks"
361                 InstancePrefix "toast_hit"
362                 InstancesFrom "schemaname" "relname"
363                 ValuesFrom "toast_hit"
364         </Result>
365         <Result>
366                 Type "pg_blks"
367                 InstancePrefix "tidx_read"
368                 InstancesFrom "schemaname" "relname"
369                 ValuesFrom "tidx_read"
370         </Result>
371         <Result>
372                 Type "pg_blks"
373                 InstancePrefix "tidx_hit"
374                 InstancesFrom "schemaname" "relname"
375                 ValuesFrom "tidx_hit"
376         </Result>
377 </Query>
378
379 <Query disk_usage>
380         Statement "SELECT pg_database_size($1) AS size;"
381
382         Param database
383
384         <Result>
385                 Type pg_db_size
386                 ValuesFrom "size"
387         </Result>
388 </Query>
389
390 # vim: set ft=config :
391