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
|
-- For objects that are newly created, we need to set the initprivs to
-- the initprivs for some table that was created in the installation
-- of the TimescaleDB extension and not as part of any update.
--
-- We chose the "chunk" catalog table for this since that is created
-- in the first version of TimescaleDB and should have the correct
-- initprivs, but we could use any other table that existed in the
-- first installation.
INSERT INTO _timescaledb_internal.saved_privs
SELECT nspname, relname, relacl,
(SELECT tmpini FROM _timescaledb_internal.saved_privs
WHERE tmpnsp = '_timescaledb_catalog' AND tmpname = 'chunk')
FROM pg_class JOIN pg_namespace ns ON ns.oid = relnamespace
LEFT JOIN _timescaledb_internal.saved_privs ON tmpnsp = nspname AND tmpname = relname
WHERE relkind IN ('r', 'v') AND nspname IN ('_timescaledb_catalog', '_timescaledb_config')
OR nspname = '_timescaledb_internal'
AND relname IN ('hypertable_chunk_local_size', 'compressed_chunk_stats',
'bgw_job_stat', 'bgw_policy_chunk_stats', 'job_errors')
ON CONFLICT DO NOTHING;
-- The above is good enough for tables and views. However sequences need to
-- use the "chunk_id_seq" catalog sequence as a template
INSERT INTO _timescaledb_internal.saved_privs
SELECT nspname, relname, relacl,
(SELECT tmpini FROM _timescaledb_internal.saved_privs
WHERE tmpnsp = '_timescaledb_catalog' AND tmpname = 'chunk_id_seq')
FROM pg_class JOIN pg_namespace ns ON ns.oid = relnamespace
LEFT JOIN _timescaledb_internal.saved_privs ON tmpnsp = nspname AND tmpname = relname
WHERE relkind IN ('S') AND nspname IN ('_timescaledb_catalog', '_timescaledb_config')
OR nspname = '_timescaledb_internal'
AND relname IN ('hypertable_chunk_local_size', 'compressed_chunk_stats',
'bgw_job_stat', 'bgw_policy_chunk_stats')
ON CONFLICT DO NOTHING;
-- We can now copy back saved initprivs.
WITH to_update AS (
SELECT objoid, tmpini
FROM pg_class cl JOIN pg_namespace ns ON ns.oid = relnamespace
JOIN pg_init_privs ip ON ip.objoid = cl.oid AND ip.objsubid = 0
JOIN _timescaledb_internal.saved_privs ON tmpnsp = nspname AND tmpname = relname)
UPDATE pg_init_privs
SET initprivs = tmpini
FROM to_update
WHERE to_update.objoid = pg_init_privs.objoid
AND classoid = 'pg_class'::regclass
AND objsubid = 0;
-- Can only restore permissions on views after they have been rebuilt,
-- so we restore for all types of objects here.
WITH to_update AS (
SELECT cl.oid, tmpacl
FROM pg_class cl JOIN pg_namespace ns ON ns.oid = relnamespace
JOIN _timescaledb_internal.saved_privs ON tmpnsp = nspname AND tmpname = relname)
UPDATE pg_class cl SET relacl = tmpacl
FROM to_update WHERE cl.oid = to_update.oid;
DROP TABLE _timescaledb_internal.saved_privs;
-- Create watermark record when required
DO
$$
DECLARE
ts_version TEXT;
BEGIN
SELECT extversion INTO ts_version FROM pg_extension WHERE extname = 'timescaledb';
IF ts_version >= '2.11.0' THEN
INSERT INTO _timescaledb_catalog.continuous_aggs_watermark (mat_hypertable_id, watermark)
SELECT a.mat_hypertable_id, _timescaledb_functions.cagg_watermark_materialized(a.mat_hypertable_id)
FROM _timescaledb_catalog.continuous_agg a
LEFT JOIN _timescaledb_catalog.continuous_aggs_watermark b ON b.mat_hypertable_id = a.mat_hypertable_id
WHERE b.mat_hypertable_id IS NULL
ORDER BY 1;
END IF;
END;
$$;
-- Repair relations that have relacl entries for users that do not
-- exist in pg_authid
CALL _timescaledb_functions.repair_relation_acls();
-- Cleanup orphaned compression settings
WITH orphaned_settings AS (
SELECT cs.relid, cl.relname
FROM _timescaledb_catalog.compression_settings cs
LEFT JOIN pg_class cl ON (cs.relid = cl.oid)
WHERE cl.relname IS NULL
)
DELETE FROM _timescaledb_catalog.compression_settings AS cs
USING orphaned_settings AS os WHERE cs.relid = os.relid;
|
