@@ -78,6 +78,7 @@ $$ LANGUAGE plpgsql;
78
78
79
79
DROP FUNCTION public .top_time_queries ;
80
80
DROP FUNCTION public .aqo_drop ;
81
+ DROP FUNCTION public .clean_aqo_data ;
81
82
82
83
--
83
84
-- Show execution time of queries, for which AQO has statistics.
@@ -166,4 +167,45 @@ END;
166
167
$$ LANGUAGE plpgsql;
167
168
168
169
COMMENT ON FUNCTION public.aqo_drop_class(bigint) IS
169
- ' Remove info about an query class from AQO ML knowledge base.' ;
170
+ ' Remove info about an query class from AQO ML knowledge base.' ;
171
+
172
+ --
173
+ -- Remove unneeded rows from the AQO ML storage.
174
+ -- For common feature space, remove rows from aqo_data only.
175
+ -- For custom feature space - remove all rows related to the space from all AQO
176
+ -- tables even if only one oid for one feature subspace of the space is illegal.
177
+ -- Returns number of deleted rows from aqo_queries and aqo_data tables.
178
+ --
179
+ CREATE OR REPLACE FUNCTION public .aqo_cleanup(OUT nfs integer , OUT nfss integer )
180
+ AS $$
181
+ DECLARE
182
+ fs bigint ;
183
+ fss integer ;
184
+ BEGIN
185
+ -- Save current number of rows
186
+ SELECT count (* ) FROM aqo_queries INTO nfs;
187
+ SELECT count (* ) FROM aqo_data INTO nfss;
188
+
189
+ FOR fs,fss IN SELECT q1 .fs ,q1 .fss FROM (
190
+ SELECT fspace_hash fs, fsspace_hash fss, unnest(oids) AS reloid
191
+ FROM aqo_data) AS q1
192
+ WHERE q1 .reloid NOT IN (SELECT oid FROM pg_class)
193
+ GROUP BY (q1 .fs ,q1 .fss )
194
+ LOOP
195
+ IF (fs = 0 ) THEN
196
+ DELETE FROM aqo_data WHERE fsspace_hash = fss;
197
+ continue;
198
+ END IF;
199
+
200
+ -- Remove ALL feature space if one of oids isn't exists
201
+ DELETE FROM aqo_queries WHERE fspace_hash = fs;
202
+ END LOOP;
203
+
204
+ -- Calculate difference with previous state of knowledge base
205
+ nfs := nfs - (SELECT count (* ) FROM aqo_queries);
206
+ nfss := nfss - (SELECT count (* ) FROM aqo_data);
207
+ END;
208
+ $$ LANGUAGE plpgsql;
209
+
210
+ COMMENT ON FUNCTION public.aqo_cleanup() IS
211
+ ' Remove unneeded rows from the AQO ML storage' ;
0 commit comments