forked from timescale/timescaledb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
latest-dev.sql
309 lines (280 loc) · 12.9 KB
/
latest-dev.sql
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
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
-- gapfill with timezone support
CREATE FUNCTION @extschema@.time_bucket_gapfill(bucket_width INTERVAL, ts TIMESTAMPTZ, timezone TEXT, start TIMESTAMPTZ=NULL, finish TIMESTAMPTZ=NULL) RETURNS TIMESTAMPTZ
AS '@MODULE_PATHNAME@', 'ts_gapfill_timestamptz_timezone_bucket' LANGUAGE C VOLATILE PARALLEL SAFE;
ALTER TABLE _timescaledb_catalog.compression_chunk_size DROP CONSTRAINT compression_chunk_size_pkey;
ALTER TABLE _timescaledb_catalog.compression_chunk_size ADD CONSTRAINT compression_chunk_size_pkey PRIMARY KEY(chunk_id);
CREATE TABLE _timescaledb_internal.job_errors (
job_id integer not null,
pid integer,
start_time timestamptz,
finish_time timestamptz,
error_data jsonb
);
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_internal.job_errors', '');
CREATE VIEW timescaledb_information.job_errors AS
SELECT
job_id,
error_data ->> 'proc_schema' as proc_schema,
error_data ->> 'proc_name' as proc_name,
pid,
start_time,
finish_time,
error_data ->> 'sqlerrcode' AS sqlerrcode,
CASE WHEN error_data ->>'message' IS NOT NULL THEN
CASE WHEN error_data ->>'detail' IS NOT NULL THEN
CASE WHEN error_data ->>'hint' IS NOT NULL THEN concat(error_data ->>'message', '. ', error_data ->>'detail', '. ', error_data->>'hint')
ELSE concat(error_data ->>'message', ' ', error_data ->>'detail')
END
ELSE
CASE WHEN error_data ->>'hint' IS NOT NULL THEN concat(error_data ->>'message', '. ', error_data->>'hint')
ELSE error_data ->>'message'
END
END
ELSE
'job crash detected, see server logs'
END
AS err_message
FROM
_timescaledb_internal.job_errors;
-- drop dependent views
DROP VIEW IF EXISTS timescaledb_information.job_stats;
DROP VIEW IF EXISTS timescaledb_information.jobs;
CREATE TABLE _timescaledb_internal._tmp_bgw_job_stat AS SELECT * FROM _timescaledb_internal.bgw_job_stat;
DROP TABLE _timescaledb_internal.bgw_job_stat;
CREATE TABLE _timescaledb_internal.bgw_job_stat (
job_id integer NOT NULL,
last_start timestamptz NOT NULL DEFAULT NOW(),
last_finish timestamptz NOT NULL,
next_start timestamptz NOT NULL,
last_successful_finish timestamptz NOT NULL,
last_run_success bool NOT NULL,
total_runs bigint NOT NULL,
total_duration interval NOT NULL,
total_successes bigint NOT NULL,
total_failures bigint NOT NULL,
total_crashes bigint NOT NULL,
consecutive_failures int NOT NULL,
consecutive_crashes int NOT NULL,
flags int NOT NULL DEFAULT 0,
-- table constraints
CONSTRAINT bgw_job_stat_pkey PRIMARY KEY (job_id),
CONSTRAINT bgw_job_stat_job_id_fkey FOREIGN KEY (job_id) REFERENCES _timescaledb_config.bgw_job (id) ON DELETE CASCADE
);
INSERT INTO _timescaledb_internal.bgw_job_stat SELECT
job_id, last_start, last_finish, next_start, last_successful_finish, last_run_success, total_runs, total_duration, total_successes, total_failures, total_crashes, consecutive_failures, consecutive_crashes, 0
FROM _timescaledb_internal._tmp_bgw_job_stat;
DROP TABLE _timescaledb_internal._tmp_bgw_job_stat;
GRANT SELECT ON TABLE _timescaledb_internal.bgw_job_stat TO PUBLIC;
DROP FUNCTION IF EXISTS _timescaledb_internal.hypertable_local_size(name, name);
DROP VIEW IF EXISTS _timescaledb_internal.hypertable_chunk_local_size;
CREATE VIEW _timescaledb_internal.hypertable_chunk_local_size AS
SELECT
h.schema_name AS hypertable_schema,
h.table_name AS hypertable_name,
h.id AS hypertable_id,
c.id AS chunk_id,
c.schema_name AS chunk_schema,
c.table_name AS chunk_name,
COALESCE((relsize).total_size, 0) AS total_bytes,
COALESCE((relsize).heap_size, 0) AS heap_bytes,
COALESCE((relsize).index_size, 0) AS index_bytes,
COALESCE((relsize).toast_size, 0) AS toast_bytes,
COALESCE((relcompsize).total_size, 0) AS compressed_total_size,
COALESCE((relcompsize).heap_size, 0) AS compressed_heap_size,
COALESCE((relcompsize).index_size, 0) AS compressed_index_size,
COALESCE((relcompsize).toast_size, 0) AS compressed_toast_size
FROM
_timescaledb_catalog.hypertable h
JOIN _timescaledb_catalog.chunk c ON h.id = c.hypertable_id
AND c.dropped IS FALSE
JOIN LATERAL _timescaledb_internal.relation_size(
format('%I.%I'::text, c.schema_name, c.table_name)::regclass) AS relsize ON TRUE
LEFT JOIN _timescaledb_catalog.chunk comp ON comp.id = c.compressed_chunk_id
LEFT JOIN LATERAL _timescaledb_internal.relation_size(
CASE WHEN comp.schema_name IS NOT NULL AND comp.table_name IS NOT NULL THEN
format('%I.%I', comp.schema_name, comp.table_name)::regclass
ELSE
NULL::regclass
END
) AS relcompsize ON TRUE;
CREATE FUNCTION _timescaledb_internal.hypertable_local_size(
schema_name_in name,
table_name_in name)
RETURNS TABLE (
table_bytes BIGINT,
index_bytes BIGINT,
toast_bytes BIGINT,
total_bytes BIGINT)
LANGUAGE SQL VOLATILE STRICT AS
$BODY$
/* get the main hypertable id and sizes */
WITH _hypertable AS (
SELECT
id,
_timescaledb_internal.relation_size(format('%I.%I', schema_name, table_name)::regclass) AS relsize
FROM
_timescaledb_catalog.hypertable
WHERE
schema_name = schema_name_in
AND table_name = table_name_in
),
/* project the size of the parent hypertable */
_hypertable_sizes AS (
SELECT
id,
COALESCE((relsize).total_size, 0) AS total_bytes,
COALESCE((relsize).heap_size, 0) AS heap_bytes,
COALESCE((relsize).index_size, 0) AS index_bytes,
COALESCE((relsize).toast_size, 0) AS toast_bytes,
0::BIGINT AS compressed_total_size,
0::BIGINT AS compressed_index_size,
0::BIGINT AS compressed_toast_size,
0::BIGINT AS compressed_heap_size
FROM
_hypertable
),
/* calculate the size of the hypertable chunks */
_chunk_sizes AS (
SELECT
chunk_id,
COALESCE(ch.total_bytes, 0) AS total_bytes,
COALESCE(ch.heap_bytes, 0) AS heap_bytes,
COALESCE(ch.index_bytes, 0) AS index_bytes,
COALESCE(ch.toast_bytes, 0) AS toast_bytes,
COALESCE(ch.compressed_total_size, 0) AS compressed_total_size,
COALESCE(ch.compressed_index_size, 0) AS compressed_index_size,
COALESCE(ch.compressed_toast_size, 0) AS compressed_toast_size,
COALESCE(ch.compressed_heap_size, 0) AS compressed_heap_size
FROM
_timescaledb_internal.hypertable_chunk_local_size ch
JOIN _hypertable_sizes ht ON ht.id = ch.hypertable_id
WHERE hypertable_schema = schema_name_in
AND hypertable_name = table_name_in
)
/* calculate the SUM of the hypertable and chunk sizes */
SELECT
(SUM(heap_bytes) + SUM(compressed_heap_size))::BIGINT AS heap_bytes,
(SUM(index_bytes) + SUM(compressed_index_size))::BIGINT AS index_bytes,
(SUM(toast_bytes) + SUM(compressed_toast_size))::BIGINT AS toast_bytes,
(SUM(total_bytes) + SUM(compressed_total_size))::BIGINT AS total_bytes
FROM
(SELECT * FROM _hypertable_sizes
UNION ALL
SELECT * FROM _chunk_sizes) AS sizes;
$BODY$ SET search_path TO pg_catalog, pg_temp;
DROP FUNCTION IF EXISTS @extschema@.add_retention_policy(REGCLASS, "any", BOOL, INTERVAL);
DROP FUNCTION IF EXISTS @extschema@.add_compression_policy(REGCLASS, "any", BOOL, INTERVAL);
DROP FUNCTION IF EXISTS @extschema@.add_continuous_aggregate_policy(REGCLASS, "any", "any", INTERVAL, BOOL);
DROP FUNCTION IF EXISTS @extschema@.add_job(REGPROC, INTERVAL, JSONB, TIMESTAMPTZ, BOOL, REGPROC);
DROP FUNCTION IF EXISTS @extschema@.add_reorder_policy(REGCLASS, NAME, BOOL);
DROP VIEW IF EXISTS timescaledb_information.jobs;
DROP VIEW IF EXISTS timescaledb_information.job_stats;
DROP VIEW IF EXISTS timescaledb_experimental.policies;
-- rebuild _timescaledb_config.bgw_job
CREATE TABLE _timescaledb_config.bgw_job_tmp AS SELECT * FROM _timescaledb_config.bgw_job;
ALTER EXTENSION timescaledb DROP TABLE _timescaledb_config.bgw_job;
ALTER EXTENSION timescaledb DROP SEQUENCE _timescaledb_config.bgw_job_id_seq;
ALTER TABLE _timescaledb_internal.bgw_job_stat DROP CONSTRAINT IF EXISTS bgw_job_stat_job_id_fkey;
ALTER TABLE _timescaledb_internal.bgw_policy_chunk_stats DROP CONSTRAINT IF EXISTS bgw_policy_chunk_stats_job_id_fkey;
CREATE TABLE _timescaledb_internal.tmp_bgw_job_seq_value AS SELECT last_value, is_called FROM _timescaledb_config.bgw_job_id_seq;
DROP TABLE _timescaledb_config.bgw_job;
CREATE SEQUENCE _timescaledb_config.bgw_job_id_seq MINVALUE 1000;
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job_id_seq', '');
SELECT setval('_timescaledb_config.bgw_job_id_seq', last_value, is_called) FROM _timescaledb_internal.tmp_bgw_job_seq_value;
DROP TABLE _timescaledb_internal.tmp_bgw_job_seq_value;
-- new table as we want it
CREATE TABLE _timescaledb_config.bgw_job (
id INTEGER PRIMARY KEY DEFAULT nextval('_timescaledb_config.bgw_job_id_seq'),
application_name name NOT NULL,
schedule_interval interval NOT NULL,
max_runtime interval NOT NULL,
max_retries integer NOT NULL,
retry_period interval NOT NULL,
proc_schema name NOT NULL,
proc_name name NOT NULL,
owner name NOT NULL DEFAULT CURRENT_ROLE,
scheduled bool NOT NULL DEFAULT TRUE,
fixed_schedule bool not null default true,
initial_start timestamptz,
hypertable_id integer REFERENCES _timescaledb_catalog.hypertable (id) ON DELETE CASCADE,
config jsonb ,
check_schema NAME,
check_name NAME,
timezone TEXT
);
ALTER SEQUENCE _timescaledb_config.bgw_job_id_seq OWNED BY _timescaledb_config.bgw_job.id;
CREATE INDEX bgw_job_proc_hypertable_id_idx ON _timescaledb_config.bgw_job(proc_schema,proc_name,hypertable_id);
INSERT INTO _timescaledb_config.bgw_job(
id, application_name, schedule_interval,
max_runtime, max_retries, retry_period,
proc_schema, proc_name, owner, scheduled,
hypertable_id, config, check_schema, check_name,
fixed_schedule
)
SELECT id, application_name, schedule_interval, max_runtime, max_retries, retry_period,
proc_schema, proc_name, owner, scheduled, hypertable_id, config, check_schema, check_name, FALSE
FROM _timescaledb_config.bgw_job_tmp ORDER BY id;
DROP TABLE _timescaledb_config.bgw_job_tmp;
ALTER TABLE _timescaledb_internal.bgw_job_stat ADD CONSTRAINT bgw_job_stat_job_id_fkey FOREIGN KEY(job_id) REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE;
ALTER TABLE _timescaledb_internal.bgw_policy_chunk_stats ADD CONSTRAINT bgw_policy_chunk_stats_job_id_fkey FOREIGN KEY(job_id) REFERENCES _timescaledb_config.bgw_job(id) ON DELETE CASCADE;
SELECT pg_catalog.pg_extension_config_dump('_timescaledb_config.bgw_job', 'WHERE id >= 1000');
GRANT SELECT ON _timescaledb_config.bgw_job TO PUBLIC;
GRANT SELECT ON _timescaledb_config.bgw_job_id_seq TO PUBLIC;
-- do simple CREATE for the functions with modified signatures
CREATE FUNCTION @extschema@.add_continuous_aggregate_policy(
continuous_aggregate REGCLASS, start_offset "any",
end_offset "any", schedule_interval INTERVAL,
if_not_exists BOOL = false,
initial_start TIMESTAMPTZ = NULL,
timezone TEXT = NULL)
RETURNS INTEGER
AS '@MODULE_PATHNAME@', 'ts_policy_refresh_cagg_add'
LANGUAGE C VOLATILE;
CREATE FUNCTION @extschema@.add_compression_policy(
hypertable REGCLASS, compress_after "any",
if_not_exists BOOL = false,
schedule_interval INTERVAL = NULL,
initial_start TIMESTAMPTZ = NULL,
timezone TEXT = NULL
)
RETURNS INTEGER
AS '@MODULE_PATHNAME@', 'ts_policy_compression_add'
LANGUAGE C VOLATILE;
CREATE FUNCTION @extschema@.add_retention_policy(
relation REGCLASS,
drop_after "any",
if_not_exists BOOL = false,
schedule_interval INTERVAL = NULL,
initial_start TIMESTAMPTZ = NULL,
timezone TEXT = NULL
)
RETURNS INTEGER AS '@MODULE_PATHNAME@', 'ts_policy_retention_add'
LANGUAGE C VOLATILE;
CREATE FUNCTION @extschema@.add_job(
proc REGPROC,
schedule_interval INTERVAL,
config JSONB DEFAULT NULL,
initial_start TIMESTAMPTZ DEFAULT NULL,
scheduled BOOL DEFAULT true,
check_config REGPROC DEFAULT NULL,
fixed_schedule BOOL DEFAULT TRUE,
timezone TEXT DEFAULT NULL
) RETURNS INTEGER AS '@MODULE_PATHNAME@', 'ts_job_add' LANGUAGE C VOLATILE;
CREATE FUNCTION @extschema@.add_reorder_policy(
hypertable REGCLASS,
index_name NAME,
if_not_exists BOOL = false,
initial_start timestamptz = NULL,
timezone TEXT = NULL
) RETURNS INTEGER
AS '@MODULE_PATHNAME@', 'ts_policy_reorder_add'
LANGUAGE C VOLATILE;
CREATE INDEX chunk_data_node_node_name_idx ON _timescaledb_catalog.chunk_data_node (node_name);
CREATE FUNCTION @extschema@.alter_data_node(
node_name NAME,
host TEXT = NULL,
database NAME = NULL,
port INTEGER = NULL,
options TEXT[] = NULL
) RETURNS TABLE(node_name NAME, host TEXT, port INTEGER, database NAME, options TEXT[])
AS '@MODULE_PATHNAME@', 'ts_data_node_alter' LANGUAGE C VOLATILE;