8000 Rewrite aqo_drop_class and remove some unnecessary functions from the… · postgrespro/aqo@0241215 · GitHub
[go: up one dir, main page]
Skip to content

Commit 0241215

Browse files
danolivoAlena Rybakina
danolivo
authored and
Alena Rybakina
committed
Rewrite aqo_drop_class and remove some unnecessary functions from the UI.
1 parent 2f4e6cd commit 0241215

File tree

4 files changed

+76
-117
lines changed

4 files changed

+76
-117
lines changed

‎aqo--1.4--1.5.sql

Lines changed: 26 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use "ALTER EXTENSION aqo UPDATE TO '1.5'" to load this file. \quit
55

6+
/* Remove old interface of the extension */
67
DROP FUNCTION array_mse;
78
DROP FUNCTION array_avg;
89
DROP FUNCTION public.aqo_clear_hist; -- Should be renamed and reworked
@@ -14,12 +15,15 @@ DROP FUNCTION public.aqo_status;
1415
DROP FUNCTION public.clean_aqo_data;
1516
DROP FUNCTION public.show_cardinality_errors;
1617
DROP FUNCTION public.top_time_queries;
17-
1818
DROP TABLE public.aqo_data CASCADE;
1919
DROP TABLE public.aqo_queries CASCADE;
2020
DROP TABLE public.aqo_query_texts CASCADE;
2121
DROP TABLE public.aqo_query_stat CASCADE;
2222

23+
24+
/*
25+
* VIEWs to discover AQO data.
26+
*/
2327
CREATE FUNCTION aqo_queries (
2428
OUT queryid bigint,
2529
OUT fs bigint,
@@ -30,28 +34,13 @@ CREATE FUNCTION aqo_queries (
3034
RETURNS SETOF record
3135
AS 'MODULE_PATHNAME', 'aqo_queries'
3236
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
33-
CREATE FUNCTION aqo_queries_remove(queryid bigint) RETURNS bool
34-
AS 'MODULE_PATHNAME'
35-
LANGUAGE C STRICT PARALLEL SAFE;
3637

3738
CREATE FUNCTION aqo_query_texts(OUT queryid bigint, OUT query_text text)
3839
RETURNS SETOF record
3940
AS 'MODULE_PATHNAME', 'aqo_query_texts'
4041
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
41-
CREATE FUNCTION aqo_qtexts_remove(queryid bigint) RETURNS bool
42-
AS 'MODULE_PATHNAME'
43-
LANGUAGE C STRICT PARALLEL SAFE;
44-
45-
--
46-
-- Remove all records in the AQO storage.
47-
-- Return number of rows removed.
48-
--
49-
CREATE FUNCTION aqo_reset() RETURNS bigint
50-
AS 'MODULE_PATHNAME' LANGUAGE C PARALLEL SAFE;
51-
COMMENT ON FUNCTION aqo_reset() IS
52-
'Reset all data gathered by AQO';
5342

54-
CREATE FUNCTION aqo_query_stat(
43+
CREATE FUNCTION aqo_query_stat (
5544
OUT queryid bigint,
5645
OUT execution_time_with_aqo double precision[],
5746
OUT execution_time_without_aqo double precision[],
@@ -66,7 +55,7 @@ RETURNS SETOF record
6655
AS 'MODULE_PATHNAME', 'aqo_query_stat'
6756
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
6857

69-
CREATE FUNCTION aqo_data(
58+
CREATE FUNCTION aqo_data (
7059
OUT fs bigint,
7160
OUT fss integer,
7261
OUT nfeatures integer,
@@ -78,18 +67,13 @@ CREATE FUNCTION aqo_data(
7867
RETURNS SETOF record
7968
AS 'MODULE_PATHNAME', 'aqo_data'
8069
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
81-
CREATE FUNCTION aqo_data_remove(fs bigint, fss int) RETURNS bool
82-
AS 'MODULE_PATHNAME'
83-
LANGUAGE C PARALLEL SAFE;
8470

8571
CREATE VIEW aqo_query_stat AS SELECT * FROM aqo_query_stat();
8672
CREATE VIEW aqo_query_texts AS SELECT * FROM aqo_query_texts();
8773
CREATE VIEW aqo_data AS SELECT * FROM aqo_data();
8874
CREATE VIEW aqo_queries AS SELECT * FROM aqo_queries();
8975

90-
CREATE FUNCTION aqo_stat_remove(fs bigint) RETURNS bool
91-
AS 'MODULE_PATHNAME'
92-
LANGUAGE C STRICT PARALLEL SAFE;
76+
/* UI functions */
9377

9478
--
9579
-- Show execution time of queries, for which AQO has statistics.
@@ -141,44 +125,17 @@ ELSE
141125
END IF;
142126
END;
143127
$$ LANGUAGE plpgsql;
144-
145128
COMMENT ON FUNCTION aqo_execution_time(boolean) IS
146129
'Get execution time of queries. If controlled = true (AQO could advise cardinality estimations), show time of last execution attempt. Another case (AQO not used), return an average value of execution time across all known executions.';
147130

148131
--
149-
-- Remove all information about a query class from AQO storage.
132+
-- Remove query class settings, text, statistics and ML data from AQO storage.
133+
-- Return number of FSS records, removed from the storage.
150134
--
151-
CREATE OR REPLACE FUNCTION aqo_drop_class(queryid_rm bigint)
152-
RETURNS integer AS $$
153-
DECLARE
154-
lfs bigint;
155-
num integer;
156-
BEGIN
157-
IF (queryid_rm = 0) THEN
158-
raise EXCEPTION '[AQO] Cannot remove basic class %.', queryid_rm;
159-
END IF;
160-
161-
SELECT fs FROM aqo_queries WHERE (queryid = queryid_rm) INTO lfs;
162-
163-
IF (lfs IS NULL) THEN
164-
raise WARNING '[AQO] Nothing to remove for the class %.', queryid_rm;
165-
RETURN 0;
166-
END IF;
167-
168-
IF (lfs <> queryid_rm) THEN
169-
raise WARNING '[AQO] Removing query class has non-generic feature space value: id = %, fs = %.', queryid_rm, fs;
170-
END IF;
171-
172-
SELECT count(*) FROM aqo_data WHERE fs = lfs INTO num;
173-
174-
PERFORM aqo_queries_remove(queryid_rm);
175-
PERFORM aqo_stat_remove(queryid_rm);
176-
PERFORM aqo_qtexts_remove(queryid_rm);
177-
PERFORM aqo_data_remove(lfs, NULL);
178-
RETURN num;
179-
END;
180-
$$ LANGUAGE plpgsql;
181-
135+
CREATE OR REPLACE FUNCTION aqo_drop_class(queryid bigint)
136+
RETURNS integer
137+
AS 'MODULE_PATHNAME', 'aqo_drop_class'
138+
LANGUAGE C STRICT VOLATILE;
182139
COMMENT ON FUNCTION aqo_drop_class(bigint) IS
183140
'Remove info about an query class from AQO ML knowledge base.';
184141

@@ -190,9 +147,8 @@ COMMENT ON FUNCTION aqo_drop_class(bigint) IS
190147
-- Returns number of deleted rows from aqo_queries and aqo_data tables.
191148
--
192149
CREATE OR REPLACE FUNCTION aqo_cleanup(OUT nfs integer, OUT nfss integer)
193-
AS 'MODULE_PATHNAME'
150+
AS 'MODULE_PATHNAME', 'aqo_cleanup'
194151
LANGUAGE C STRICT VOLATILE;
195-
196152
COMMENT ON FUNCTION aqo_cleanup() IS
197153
'Remove unneeded rows from the AQO ML storage';
198154

@@ -327,4 +283,14 @@ CREATE FUNCTION aqo_queries_update(queryid bigint, fs bigint, learn_aqo bool,
327283
use_aqo bool, auto_tuning bool)
328284
RETURNS bool
329285
AS 'MODULE_PATHNAME', 'aqo_queries_update'
330-
LANGUAGE C VOLATILE;
286+
LANGUAGE C VOLATILE;
287+
288+
--
289+
-- Remove all records in the AQO storage.
290+
-- Return number of rows removed.
291+
--
292+
CREATE FUNCTION aqo_reset() RETURNS bigint
293+
AS 'MODULE_PATHNAME', 'aqo_reset'
294+
LANGUAGE C PARALLEL SAFE;
295+
COMMENT ON FUNCTION aqo_reset() IS
296+
'Reset all data gathered by AQO';

‎expected/aqo_learn.out

Lines changed: 1 addition & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -485,14 +485,8 @@ SELECT * FROM check_estimated_rows('
485485
SET aqo.mode = 'learn';
486486
SELECT * FROM aqo_drop_class(0);
487487
ERROR: [AQO] Cannot remove basic class 0.
488-
CONTEXT: PL/pgSQL function aqo_drop_class(bigint) line 7 at RAISE
489488
SELECT * FROM aqo_drop_class(42);
490-
WARNING: [AQO] Nothing to remove for the class 42.
491-
aqo_drop_class
492-
----------------
493-
0
494-
(1 row)
495-
489+
ERROR: [AQO] Nothing to remove for the class 42.
496490
-- Remove all data from ML knowledge base
497491
SELECT count(*) FROM (
498492
SELECT aqo_drop_class(q1.id::bigint) FROM (

‎expected/gucs.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,7 @@ SELECT obj_description('aqo_reset'::regproc::oid);
9292
List of functions
9393
Schema | Name | Result data type | Argument data types | Type
9494
--------+----------------+------------------+---------------------+------
95-
public | aqo_drop_class | integer | queryid_rm bigint | func
95+
public | aqo_drop_class | integer | queryid bigint | func
9696
(1 row)
9797

9898
\df aqo_cleanup

‎storage.c

Lines changed: 48 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -96,15 +96,12 @@ PG_FUNCTION_INFO_V1(aqo_query_stat);
9696
PG_FUNCTION_INFO_V1(aqo_query_texts);
9797
PG_FUNCTION_INFO_V1(aqo_data);
9898
PG_FUNCTION_INFO_V1(aqo_queries);
99-
PG_FUNCTION_INFO_V1(aqo_stat_remove);
100-
PG_FUNCTION_INFO_V1(aqo_qtexts_remove);
101-
PG_FUNCTION_INFO_V1(aqo_data_remove);
102-
PG_FUNCTION_INFO_V1(aqo_queries_remove);
10399
PG_FUNCTION_INFO_V1(aqo_enable_query);
104100
PG_FUNCTION_INFO_V1(aqo_disable_query);
105101
PG_FUNCTION_INFO_V1(aqo_queries_update);
106102
PG_FUNCTION_INFO_V1(aqo_reset);
107103
PG_FUNCTION_INFO_V1(aqo_cleanup);
104+
PG_FUNCTION_INFO_V1(aqo_drop_class);
108105

109106

110107
bool
@@ -399,15 +396,6 @@ aqo_stat_reset(void)
399396
return num_remove;
400397
}
401398

402-
403-
Datum
404-
aqo_stat_remove(PG_FUNCTION_ARGS)
405-
{
406-
uint64 queryid = (uint64) PG_GETARG_INT64(0);
407-
408-
PG_RETURN_BOOL(_aqo_stat_remove(queryid));
409-
}
410-
411399
static void *
412400_form_stat_record_cb(void *ctx, size_t *size)
413401
{
@@ -1212,14 +1200,6 @@ _aqo_data_remove(data_key *key)
12121200
return found;
12131201
}
12141202

1215-
Datum
1216-
aqo_qtexts_remove(PG_FUNCTION_ARGS)
1217-
{
1218-
uint64 queryid = (uint64) PG_GETARG_INT64(0);
1219-
1220-
PG_RETURN_BOOL(_aqo_qtexts_remove(queryid));
1221-
}
1222-
12231203
static long
12241204
aqo_qtexts_reset(void)
12251205
{
@@ -1693,26 +1673,6 @@ _aqo_data_clean(uint64 fs)
16931673
return removed;
16941674
}
16951675

1696-
Datum
1697-
aqo_data_remove(PG_FUNCTION_ARGS)
1698-
{
1699-
data_key key;
1700-
bool found;
1701-
1702-
dsa_init();
1703-
1704-
if (PG_ARGISNULL(1))
1705-
{
1706-
/* Remove all feature subspaces from the space */
1707-
found = (_aqo_data_clean((uint64) PG_GETARG_INT64(0)) > 0);
1708-
return found;
1709-
}
1710-
1711-
key.fs = (uint64) PG_GETARG_INT64(0);
1712-
key.fss = PG_GETARG_INT32(1);
1713-
PG_RETURN_BOOL(_aqo_data_remove(&key));
1714-
}
1715-
17161676
static long
17171677
aqo_data_reset(void)
17181678
{
@@ -1802,14 +1762,6 @@ aqo_queries(PG_FUNCTION_ARGS)
18021762
return (Datum) 0;
18031763
}
18041764

1805-
Datum
1806-
aqo_queries_remove(PG_FUNCTION_ARGS)
1807-
{
1808-
uint64 queryid = (uint64) PG_GETARG_INT64(0);
1809-
1810-
PG_RETURN_BOOL(_aqo_queries_remove(queryid));
1811-
}
1812-
18131765
bool
18141766
aqo_queries_store(uint64 queryid,
18151767
uint64 fs, bool learn_aqo, bool use_aqo, bool auto_tuning)
@@ -2204,3 +2156,50 @@ aqo_cleanup(PG_FUNCTION_ARGS)
22042156
tuplestore_donestoring(tupstore);
22052157
return (Datum) 0;
22062158
}
2159+
2160+
/*
2161+
* XXX: Maybe to allow usage of NULL value to make a reset?
2162+
*/
2163+
Datum
2164+
aqo_drop_class(PG_FUNCTION_ARGS)
2165+
{
2166+
uint64 queryid = PG_GETARG_INT64(0);
2167+
bool found;
2168+
QueriesEntry *entry;
2169+
uint64 fs;
2170+
long cnt;
2171+
2172+
if (queryid == 0)
2173+
elog(ERROR, "[AQO] Cannot remove basic class %lu.", queryid);
2174+
2175+
/* Extract FS value for the queryid */
2176+
LWLockAcquire(&aqo_state->queries_lock, LW_SHARED);
2177+
entry = (QueriesEntry *) hash_search(queries_htab, &queryid, HASH_FIND,
2178+
&found);
2179+
if (!found)
2180+
elog(ERROR, "[AQO] Nothing to remove for the class %lu.", queryid);
2181+
2182+
fs = entry->fs;
2183+
LWLockRelease(&aqo_state->queries_lock);
2184+
2185+
if (fs == 0)
2186+
elog(ERROR, "[AQO] Cannot remove class %lu with default FS.", queryid);
2187+
if (fs != queryid)
2188+
elog(WARNING,
2189+
"[AQO] Removing query class has non-generic feature space value: id = %lu, fs = %lu.",
2190+
queryid, fs);
2191+
2192+
/* Now, remove all data related to the class */
2193+
_aqo_queries_remove(queryid);
2194+
_aqo_stat_remove(queryid);
2195+
_aqo_qtexts_remove(queryid);
2196+
cnt = _aqo_data_clean(fs);
2197+
2198+
/* Immediately save changes to permanent storage. */
2199+
aqo_stat_flush();
2200+
aqo_data_flush();
2201+
aqo_qtexts_flush();
2202+
aqo_queries_flush();
2203+
2204+
PG_RETURN_INT32(cnt);
2205+
}

0 commit comments

Comments
 (0)
0