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
|
CREATE OR REPLACE FUNCTION cuid_timestamp(cuid varchar(25)) RETURNS integer AS $$
DECLARE
base36 varchar;
a char[];
ret bigint;
i int;
val int;
chars varchar;
BEGIN
base36 := substring(cuid FROM 2 FOR 8);
chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
FOR i IN REVERSE char_length(base36)..1 LOOP
a := a || substring(upper(base36) FROM i FOR 1)::char;
END LOOP;
i := 0;
ret := 0;
WHILE i < (array_length(a, 1)) LOOP
val := position(a[i + 1] IN chars) - 1;
ret := ret + (val * (36 ^ i));
i := i + 1;
END LOOP;
RETURN CAST(ret/1000 AS integer);
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
DROP FUNCTION IF EXISTS base36_decode(character varying);
DO $$
DECLARE
minimum_postgres_version_major INTEGER;
minimum_postgres_version_minor INTEGER;
current_postgres_version_major INTEGER;
current_postgres_version_minor INTEGER;
current_postgres_version_full VARCHAR;
minimum_timescaledb_version_major INTEGER;
minimum_timescaledb_version_minor INTEGER;
current_timescaledb_version_major INTEGER;
current_timescaledb_version_minor INTEGER;
current_timescaledb_version_full VARCHAR;
current_db_extension VARCHAR;
BEGIN
SELECT 10 INTO minimum_postgres_version_major;
SELECT 9 INTO minimum_postgres_version_minor;
SELECT 2 INTO minimum_timescaledb_version_major;
SELECT 0 INTO minimum_timescaledb_version_minor;
SHOW server_version INTO current_postgres_version_full;
IF NOT found THEN
RAISE EXCEPTION 'Cannot determine PostgreSQL version, aborting';
END IF;
SELECT substring(current_postgres_version_full, '^(\d+).') INTO current_postgres_version_major;
SELECT substring(current_postgres_version_full, '^\d+.(\d+)') INTO current_postgres_version_minor;
IF (current_postgres_version_major < minimum_postgres_version_major OR
(current_postgres_version_major = minimum_postgres_version_major AND
current_postgres_version_minor < minimum_postgres_version_minor)) THEN
RAISE EXCEPTION 'PostgreSQL version % is NOT SUPPORTED (with TimescaleDB)! Minimum is %.%.0 !',
current_postgres_version_full, minimum_postgres_version_major,
minimum_postgres_version_minor;
ELSE
RAISE NOTICE 'PostgreSQL version % is valid', current_postgres_version_full;
END IF;
SELECT extversion INTO current_timescaledb_version_full FROM pg_extension WHERE extname = 'timescaledb';
IF NOT found THEN
RAISE EXCEPTION 'TimescaleDB extension is not installed';
ELSE
RAISE NOTICE 'TimescaleDB extension is detected';
END IF;
SELECT substring(current_timescaledb_version_full, '^(\d+).') INTO current_timescaledb_version_major;
SELECT substring(current_timescaledb_version_full, '^\d+.(\d+)') INTO current_timescaledb_version_minor;
IF (current_timescaledb_version_major < minimum_timescaledb_version_major OR
(current_timescaledb_version_major = minimum_timescaledb_version_major AND
current_timescaledb_version_minor < minimum_timescaledb_version_minor)) THEN
RAISE EXCEPTION 'TimescaleDB version % is UNSUPPORTED! Minimum is %.%.0!',
current_timescaledb_version_full, minimum_timescaledb_version_major,
minimum_timescaledb_version_minor;
ELSE
RAISE NOTICE 'TimescaleDB version % is valid', current_timescaledb_version_full;
END IF;
SELECT db_extension FROM config INTO current_db_extension;
PERFORM create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('history_bin', 'clock', chunk_time_interval => 86400, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('auditlog', 'auditid', chunk_time_interval => 604800,
time_partitioning_func => 'cuid_timestamp', migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('trends', 'clock', chunk_time_interval => 2592000, migrate_data => true, if_not_exists => true);
PERFORM create_hypertable('trends_uint', 'clock', chunk_time_interval => 2592000, migrate_data => true, if_not_exists => true);
IF (current_db_extension = 'timescaledb') THEN
RAISE NOTICE 'TimescaleDB extension is already installed; not changing configuration';
ELSE
UPDATE config SET db_extension='timescaledb',hk_history_global=1,hk_trends_global=1;
UPDATE config SET compression_status=1,compress_older='7d';
END IF;
RAISE NOTICE 'TimescaleDB is configured successfully';
END $$;
|
