Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite788c57

Browse files
committed
Rewrite aqo_drop_class and remove some unnecessary functions from the UI.
1 parent62591f9 commite788c57

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
DROPFUNCTION array_mse;
78
DROPFUNCTION array_avg;
89
DROPFUNCTIONpublic.aqo_clear_hist;-- Should be renamed and reworked
@@ -14,12 +15,15 @@ DROP FUNCTION public.aqo_status;
1415
DROPFUNCTIONpublic.clean_aqo_data;
1516
DROPFUNCTIONpublic.show_cardinality_errors;
1617
DROPFUNCTIONpublic.top_time_queries;
17-
1818
DROPTABLEpublic.aqo_data CASCADE;
1919
DROPTABLEpublic.aqo_queries CASCADE;
2020
DROPTABLEpublic.aqo_query_texts CASCADE;
2121
DROPTABLEpublic.aqo_query_stat CASCADE;
2222

23+
24+
/*
25+
* VIEWs to discover AQO data.
26+
*/
2327
CREATEFUNCTIONaqo_queries (
2428
OUT queryidbigint,
2529
OUT fsbigint,
@@ -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-
CREATEFUNCTIONaqo_queries_remove(queryidbigint) RETURNS bool
34-
AS'MODULE_PATHNAME'
35-
LANGUAGE C STRICT PARALLEL SAFE;
3637

3738
CREATEFUNCTIONaqo_query_texts(OUT queryidbigint, OUT query_texttext)
3839
RETURNS SETOF record
3940
AS'MODULE_PATHNAME','aqo_query_texts'
4041
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
41-
CREATEFUNCTIONaqo_qtexts_remove(queryidbigint) 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-
CREATEFUNCTIONaqo_reset() RETURNSbigint
50-
AS'MODULE_PATHNAME' LANGUAGE C PARALLEL SAFE;
51-
COMMENT ON FUNCTION aqo_reset() IS
52-
'Reset all data gathered by AQO';
5342

54-
CREATEFUNCTIONaqo_query_stat(
43+
CREATEFUNCTIONaqo_query_stat(
5544
OUT queryidbigint,
5645
OUT execution_time_with_aqodouble precision[],
5746
OUT execution_time_without_aqodouble precision[],
@@ -66,7 +55,7 @@ RETURNS SETOF record
6655
AS'MODULE_PATHNAME','aqo_query_stat'
6756
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
6857

69-
CREATEFUNCTIONaqo_data(
58+
CREATEFUNCTIONaqo_data(
7059
OUT fsbigint,
7160
OUT fssinteger,
7261
OUT nfeaturesinteger,
@@ -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-
CREATEFUNCTIONaqo_data_remove(fsbigint, fssint) RETURNS bool
82-
AS'MODULE_PATHNAME'
83-
LANGUAGE C PARALLEL SAFE;
8470

8571
CREATEVIEWaqo_query_statASSELECT*FROM aqo_query_stat();
8672
CREATEVIEWaqo_query_textsASSELECT*FROM aqo_query_texts();
8773
CREATEVIEWaqo_dataASSELECT*FROM aqo_data();
8874
CREATEVIEWaqo_queriesASSELECT*FROM aqo_queries();
8975

90-
CREATEFUNCTIONaqo_stat_remove(fsbigint) 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 REPLACEFUNCTIONaqo_drop_class(queryid_rmbigint)
152-
RETURNSintegerAS $$
153-
DECLARE
154-
lfsbigint;
155-
numinteger;
156-
BEGIN
157-
IF (queryid_rm=0) THEN
158-
raise EXCEPTION'[AQO] Cannot remove basic class %.', queryid_rm;
159-
END IF;
160-
161-
SELECT fsFROM aqo_queriesWHERE (queryid= queryid_rm) INTO lfs;
162-
163-
IF (lfs ISNULL) THEN
164-
raise WARNING'[AQO] Nothing to remove for the class %.', queryid_rm;
165-
RETURN0;
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-
SELECTcount(*)FROM aqo_dataWHERE 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 REPLACEFUNCTIONaqo_drop_class(queryidbigint)
136+
RETURNSinteger
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 REPLACEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)
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+
CREATEFUNCTIONaqo_reset() RETURNSbigint
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
returnnum_remove;
400397
}
401398

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

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

1696-
Datum
1697-
aqo_data_remove(PG_FUNCTION_ARGS)
1698-
{
1699-
data_keykey;
1700-
boolfound;
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-
returnfound;
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
staticlong
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-
uint64queryid= (uint64)PG_GETARG_INT64(0);
1809-
1810-
PG_RETURN_BOOL(_aqo_queries_remove(queryid));
1811-
}
1812-
18131765
bool
18141766
aqo_queries_store(uint64queryid,
18151767
uint64fs,boollearn_aqo,booluse_aqo,boolauto_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+
uint64queryid=PG_GETARG_INT64(0);
2167+
boolfound;
2168+
QueriesEntry*entry;
2169+
uint64fs;
2170+
longcnt;
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)
[8]ページ先頭
©2009-2025 Movatter.jp