Home PostgreSQL A simple custom machine

A simple custom machine

by admin

A simple custom machine
Custom aggregates are one of those unique PostgreSQL features that nobody seems to understand.But as soon as you create just one really working example, the clouds will part, and you will wonder how you ever lived without such a mature feature.So let’s create such a simple machine. It will return the state, that is the most frequent value, for a logical (boolean) field.
Who might want weird and why? Well, imagine that you monitor several of your web servers day and night, and you want to have an hourly uptime picture. Let’s say every 30 seconds the state of the server is recorded in a table : TRUE – the server works, FALSE – the server is down. Then if the server has been running most of the time we want to return TRUE and if the server was idle most of the time, then respectively FALSE And if the monitoring system itself is also lying and therefore there is no data, we will return NULL
Of course, all of this can also be done with many different other mechanisms, such as using the WINDOW However, imagine if you need to work with other accumulated statistics, such as server downtime or uptime, in the same query. In that case, PostgreSQL offers you a nifty mechanism.
First, we need a statistical function that will accumulate data about the Boolean field. Usually such a function has two input parameters :

  • is the parameter where the calculated values will be stored (because this function will be called for each line);
  • parameter of the column type, which will contain the value for the current row.

Suppose we want to store the number of UP and DOWN readings for a server. We can use an integer array for this. We might as well do it with a composite type, translator’s note It is easy to write such a function even in pure SQL:

CREATE OR REPLACE function mode_bool_state(int[], boolean) RETURNS int[]LANGUAGE sqlas $body$SELECT CASE $2WHEN TRUE THENarray[ $1[1] + 1, $1[2] ]WHEN FALSE THENarray[ $1[1], $1[2] + 1 ]ELSE$1END;$body$;

Note that the result of int[] will be passed as the first parameter to the input of the same function when called for the next line, translator’s note
To decide and output the final result, let’s create another function :

CREATE OR REPLACE FUNCTION mode_bool_final(INT[]) RETURNS booleanLANGUAGE sqlas $body$SELECT CASE WHEN ( $1[1] = 0 AND $1[2] = 0 )THEN NULLELSE $1[1] > = $1[2]END;$body$;

It’s just a matter of declaring the unit :

CREATE AGGREGATE mode(boolean) (SFUNC= mode_bool_state, STYPE= INT[], FINALFUNC= mode_bool_final, INITCOND= '{0, 0}');

Here SFUNC and FINALFUNC – our function names, STYPE – is the type of data to collect statistics, INITCOND – initial conditions.
Let’s see how it works!

SELECT server_name, sum(CASE WHEN server_up THEN 0.5 ELSE 0 END) as minutes_up, mode(server_up) as modeFROM serversWHERE montime BETWEEN '2013-04-01' and '2013-04-01 01:00:00';

 server_name minutes_up modeweb1 56.5 TRUEweb2 0.0 FALSEweb3 48.0 TRUEweb4 11.5 FALSE 

PS Article in English by Tom Brown also talks about how to create a custom aggregate. In it, the author does not use the terminating optional function FINALFUNC , since the type for data collection is STYPE in his example is the same as the base type of the unit.

You may also like