forked from timescale/timescaledb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
reverse-dev.sql
119 lines (105 loc) · 4.85 KB
/
reverse-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
-- gapfill with timezone support
DROP FUNCTION @extschema@.time_bucket_gapfill(INTERVAL,TIMESTAMPTZ,TEXT,TIMESTAMPTZ,TIMESTAMPTZ);
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,compressed_chunk_id);
DROP FUNCTION _timescaledb_internal.policy_job_error_retention(integer, JSONB);
DROP FUNCTION _timescaledb_internal.policy_job_error_retention_check(JSONB);
DELETE FROM _timescaledb_config.bgw_job WHERE id = 2;
ALTER EXTENSION timescaledb DROP VIEW timescaledb_information.job_errors;
ALTER EXTENSION timescaledb DROP TABLE _timescaledb_internal.job_errors;
DROP VIEW timescaledb_information.job_errors;
DROP TABLE _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,
-- 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
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 VIEW _timescaledb_internal.hypertable_chunk_local_size;
DROP FUNCTION _timescaledb_internal.hypertable_local_size(name, name);
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
)
/* 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 INDEX IF EXISTS chunk_data_node_node_name_idx;