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
|
-- General setup
\set SHOW_CONTEXT never
-- Check the source query retrieval
SELECT * FROM "PoWA".powa_catalog_src_query('pg_class', 90600);
-- check pg_database catalog snapshot. We just insert 2 databases to make
-- things simpler
INSERT INTO "PoWA".powa_catalog_database_src_tmp
SELECT 1, *
FROM "PoWA".powa_catalog_database_src(0) src
WHERE src.datname = current_database();
INSERT INTO "PoWA".powa_catalog_database_src_tmp
SELECT 1, *
FROM "PoWA".powa_catalog_database_src(0) src
WHERE src.datname != current_database()
AND src.datname != 'test'
AND src.oid != 1
LIMIT 1;
SELECT "PoWA".powa_catalog_database_snapshot(1);
-- there shouldn't be a refresh time
SELECT count(*) FILTER (WHERE last_refresh IS NULL) = 2 AS ok,
count(*) FILTER (WHERE last_refresh IS NOT NULL) AS nb_with_refresh
FROM "PoWA".powa_catalog_databases
WHERE srvid = 1;
-- check the rest of the per-database catalog snapshot
DO $_$
DECLARE
v_ok boolean;
v_num integer;
v_nb_rec integer;
v_nb_rec2 integer;
v_catname text;
v_prefix text;
v_src_tmp text;
v_query text;
BEGIN
SELECT setting INTO v_num
FROM pg_settings
WHERE name = 'server_version_num';
FOR v_catname IN SELECT catname FROM "PoWA".powa_catalogs ORDER BY priority
LOOP
-- get the necessary object name
SELECT 'powa_catalog_' || replace(v_catname, 'pg_', '') INTO v_prefix;
SELECT v_prefix || '_src_tmp'
INTO v_src_tmp;
SELECT "PoWA".powa_catalog_src_query(v_catname, v_num)
INTO v_query;
-- there shouldn't be any stored data for this catalog
EXECUTE format('SELECT count(*) = 0, count(*) FROM "PoWA".%I', v_prefix)
INTO v_ok, v_nb_rec;
IF NOT v_ok THEN
RAISE WARNING 'catalog % already has stored data (% rows) in %',
v_catname, v_nb_rec, v_prefix;
END IF;
-- manually insert some data for this catalog for 2 different
-- databases, with the same content
EXECUTE format('INSERT INTO "PoWA".%I
SELECT 1 AS srvid, d.oid AS dbid, src.*
FROM (%s) src
CROSS JOIN "PoWA".powa_catalog_databases d',
v_src_tmp, v_query);
-- snapshot the given catalog
PERFORM "PoWA".powa_catalog_generic_snapshot(1, v_catname);
-- there should now be stored data for this catalog
EXECUTE format('SELECT count(*) > 0, count(*) FROM "PoWA".%I', v_prefix)
INTO v_ok, v_nb_rec;
IF NOT v_ok THEN
RAISE WARNING 'catalog % does not have stored data in %',
v_catname, v_prefix;
END IF;
-- source table should now be empty
EXECUTE format('SELECT count(*) = 0, count(*) FROM "PoWA".%I', v_src_tmp)
INTO v_ok, v_nb_rec;
IF NOT v_ok THEN
RAISE WARNING 'source table % for catalog % still has % rows',
v_src_tmp, v_catname, v_nb_rec;
END IF;
-- There should be records for 2 databases
EXECUTE format('SELECT count(DISTINCT dbid) = 2, count(DISTINCT dbid)
FROM "PoWA".%I', v_prefix)
INTO v_ok, v_nb_rec;
IF NOT v_ok THEN
RAISE WARNING 'table % for catalog % does not have record for 2 databases (found %)',
v_prefix, v_catname, v_nb_rec;
END IF;
-- both databases should have the same number of records
EXECUTE format('SELECT (
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname = current_database()
) = (
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname != current_database()
),(
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname = current_database()
), (
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname != current_database()
)
', v_prefix, v_nb_rec, v_nb_rec2) INTO v_ok;
IF NOT v_ok THEN
RAISE WARNING 'table % for catalog % does not have the same number of records for the 2 databases: % vs %',
v_prefix, v_catname, v_nb_rec, v_nb_rec2;
END IF;
-- the refresh time should have been saved only if this is pg_class
-- catalog
SELECT count(*) INTO v_nb_rec
FROM "PoWA".powa_catalog_databases
WHERE srvid = 1
AND last_refresh IS NULL;
IF v_catname = 'pg_class' THEN
IF v_nb_rec != 0 THEN
RAISE WARNING 'last_refresh was not saved when processing pg_class, % records without a refresh time',
v_nb_rec;
END IF;
ELSE
IF v_nb_rec = 0 THEN
RAISE WARNING 'last_refresh was saved when processing %, % records without a refresh time',
v_catname, v_nb_rec;
END IF;
END IF;
-- snapshot the given catalog again without source data, nothing should
-- happen
PERFORM "PoWA".powa_catalog_generic_snapshot(1, v_catname);
-- there should still be stored data for this catalog
EXECUTE format('SELECT count(*) > 0, count(*) FROM "PoWA".%I', v_prefix)
INTO v_ok, v_nb_rec;
IF NOT v_ok THEN
RAISE WARNING 'catalog % does not have stored data in %',
v_catname, v_prefix;
END IF;
-- re-add some data in the src table, but for 1 db only, and snapshot
-- again
EXECUTE format('INSERT INTO "PoWA".%I
SELECT 1 AS srvid, d.oid AS dbid, src.*
FROM (%s) src
JOIN "PoWA".powa_catalog_databases d
ON d.datname = current_database()',
v_src_tmp, v_query);
PERFORM "PoWA".powa_catalog_generic_snapshot(1, v_catname);
-- both databases should still have the same number of records
EXECUTE format('SELECT (
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname = current_database()
) = (
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname != current_database()
),(
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname = current_database()
), (
SELECT count(*) FROM "PoWA".%1$I c
JOIN pg_database d ON c.dbid = d.oid
WHERE srvid = 1 AND d.datname != current_database()
)
', v_prefix, v_nb_rec, v_nb_rec2) INTO v_ok;
IF NOT v_ok THEN
RAISE WARNING 'table % for catalog % does not have the same number of records for the 2 databases: % vs %',
v_prefix, v_catname, v_nb_rec, v_nb_rec2;
END IF;
-- re-add some data in the src table to later test the reset
EXECUTE format('INSERT INTO "PoWA".%I
SELECT 1 AS srvid, d.oid AS dbid, src.*
FROM (%s) src
CROSS JOIN "PoWA".powa_catalog_databases d',
v_src_tmp, v_query);
END LOOP;
END;
$_$ LANGUAGE plpgsql;
SELECT catname, substr(query_source, 1, 12) AS query_source, tmp_table,
array_upper(excluded_dbnames, 1) AS nb_excluded
FROM "PoWA".powa_catalog_functions(1, 150000)
WHERE catname = 'pg_class';
-- Check the refresh interval filtering
INSERT INTO "PoWA".powa_catalog_databases(srvid, oid, datname, last_refresh)
VALUES (1, 1, 'test', now() - interval '1 month');
-- default interval should exclude test database
WITH e AS (SELECT DISTINCT unnest(excluded_dbnames) AS excluded
FROM "PoWA".powa_catalog_functions(1, 150000))
SELECT coalesce(array_agg(excluded), '{}') AS excluded_dbnames
FROM e
WHERE excluded = 'test';
-- 15 days interval should not exclude test database
WITH e AS (SELECT DISTINCT unnest(excluded_dbnames) AS excluded
FROM "PoWA".powa_catalog_functions(1, 150000, '15 days'))
SELECT coalesce(array_agg(excluded), '{}') AS excluded_dbnames
FROM e
WHERE excluded = 'test';
|
