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

Commit62591f9

Browse files
committed
One more step to make AQO relocatable. Replace stored procedure aqo_cleanup()
with the one, implemented in C.BTW, fix issue, then AQO takes control on queries, involving only a setof TEMP tables. Now AQO learn on queries with at least one plane table,permanently stored in a database.Fix regression and TAP tests: somewhere because of changed behaviour, somewhereit caused by mistakes.
1 parentd40ade2 commit62591f9

17 files changed

+382
-144
lines changed

‎aqo--1.4--1.5.sql

Lines changed: 3 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ CREATE FUNCTION aqo_query_stat(
5959
OUT planning_time_without_aqodouble precision[],
6060
OUT cardinality_error_with_aqodouble precision[],
6161
OUT cardinality_error_without_aqodouble precision[],
62-
OUT executions_with_aqobigint,
62+
OUT executions_with_aqobigint,
6363
OUT executions_without_aqobigint
6464
)
6565
RETURNS SETOF record
@@ -190,38 +190,8 @@ COMMENT ON FUNCTION aqo_drop_class(bigint) IS
190190
-- Returns number of deleted rows from aqo_queries and aqo_data tables.
191191
--
192192
CREATE OR REPLACEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)
193-
AS $$
194-
DECLARE
195-
lfsbigint;
196-
lfssinteger;
197-
BEGIN
198-
-- Save current number of rows
199-
SELECTcount(*)FROM aqo_queries INTO nfs;
200-
SELECTcount(*)FROM aqo_data INTO nfss;
201-
202-
FOR lfs,lfssINSELECTq1.fs,q1.fssFROM (
203-
SELECT fs, fss, unnest(oids)AS reloid
204-
FROM aqo_data)AS q1
205-
WHEREq1.reloid NOTIN (SELECToidFROM pg_class)
206-
GROUP BY (q1.fs,q1.fss)
207-
LOOP
208-
-- IF (fs = 0) THEN
209-
-- DELETE FROM aqo_data WHERE fsspace_hash = fss;
210-
-- continue;
211-
-- END IF;
212-
213-
-- Remove ALL feature space if one of oids isn't exists
214-
PERFORM aqo_queries_remove(lfs);
215-
PERFORM aqo_stat_remove(lfs);
216-
PERFORM aqo_qtexts_remove(lfs);
217-
PERFORM aqo_data_remove(lfs,NULL);
218-
END LOOP;
219-
220-
-- Calculate difference with previous state of knowledge base
221-
nfs := nfs- (SELECTcount(*)FROM aqo_queries);
222-
nfss := nfss- (SELECTcount(*)FROM aqo_data);
223-
END;
224-
$$ LANGUAGE plpgsql;
193+
AS'MODULE_PATHNAME'
194+
LANGUAGE C STRICT VOLATILE;
225195

226196
COMMENT ON FUNCTION aqo_cleanup() IS
227197
'Remove unneeded rows from the AQO ML storage';

‎aqo_shared.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -216,25 +216,25 @@ aqo_init_shmem(void)
216216

217217
info.keysize=sizeof(((StatEntry*)0)->queryid);
218218
info.entrysize=sizeof(StatEntry);
219-
stat_htab=ShmemInitHash("AQO Stat HTAB",64,fs_max_items,
219+
stat_htab=ShmemInitHash("AQO Stat HTAB",fs_max_items,fs_max_items,
220220
&info,HASH_ELEM |HASH_BLOBS);
221221

222222
/* Init shared memory table for query texts */
223223
info.keysize=sizeof(((QueryTextEntry*)0)->queryid);
224224
info.entrysize=sizeof(QueryTextEntry);
225-
qtexts_htab=ShmemInitHash("AQO Query Texts HTAB",64,fs_max_items,
225+
qtexts_htab=ShmemInitHash("AQO Query Texts HTAB",fs_max_items,fs_max_items,
226226
&info,HASH_ELEM |HASH_BLOBS);
227227

228228
/* Shared memory hash table for the data */
229229
info.keysize=sizeof(data_key);
230230
info.entrysize=sizeof(DataEntry);
231-
data_htab=ShmemInitHash("AQO Data HTAB",64,fss_max_items,
231+
data_htab=ShmemInitHash("AQO Data HTAB",fss_max_items,fss_max_items,
232232
&info,HASH_ELEM |HASH_BLOBS);
233233

234234
/* Shared memory hash table for queries */
235235
info.keysize=sizeof(((QueriesEntry*)0)->queryid);
236236
info.entrysize=sizeof(QueriesEntry);
237-
queries_htab=ShmemInitHash("AQO Queries HTAB",64,fs_max_items,
237+
queries_htab=ShmemInitHash("AQO Queries HTAB",fs_max_items,fs_max_items,
238238
&info,HASH_ELEM |HASH_BLOBS);
239239

240240
LWLockRelease(AddinShmemInitLock);

‎aqo_shared.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@ typedef struct AQOSharedState
3030

3131
/* Storage fields */
3232
LWLockstat_lock;/* lock for access to stat storage */
33+
boolstat_changed;
3334

3435
LWLockqtexts_lock;/* Lock for shared fields below */
3536
dsa_handleqtexts_dsa_handler;/* DSA area for storing of query texts */

‎expected/aqo_learn.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -236,10 +236,10 @@ SELECT count(*) FROM tmp1;
236236
(1 row)
237237

238238
-- Remove data on some unneeded instances of tmp1 table.
239-
SELECT aqo_cleanup();
240-
aqo_cleanup
241-
-------------
242-
(9,18)
239+
SELECT* FROMaqo_cleanup();
240+
nfs | nfss
241+
-----+------
242+
9 | 18
243243
(1 row)
244244

245245
-- Result of the query below should be empty

‎expected/forced_stat_collection.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,4 +55,11 @@ SELECT query_text FROM aqo_query_texts ORDER BY (md5(query_text));
5555
SELECT count(*) FROM person WHERE age<18 AND passport IS NOT NULL;
5656
(3 rows)
5757

58+
DROP TABLE person;
59+
SELECT 1 FROM aqo_reset(); -- Full remove of ML data before the end
60+
?column?
61+
----------
62+
1
63+
(1 row)
64+
5865
DROP EXTENSION aqo;

‎expected/temp_tables.out

Lines changed: 19 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -16,10 +16,11 @@ SELECT count(*) FROM tt AS t1, tt AS t2;
1616
0
1717
(1 row)
1818

19-
SELECT * FROM aqo_data;
20-
fs | fss | nfeatures | features | targets | reliability | oids
21-
----+-----+-----------+----------+---------+-------------+------
22-
(0 rows)
19+
SELECT query_text FROM aqo_query_texts; -- Default row should be returned
20+
query_text
21+
---------------------------------------
22+
COMMON feature space (do not delete!)
23+
(1 row)
2324

2425
-- Should be stored in the ML base
2526
SELECT count(*) FROM pt;
@@ -40,30 +41,30 @@ SELECT count(*) FROM pt AS pt1, tt AS tt1, tt AS tt2, pt AS pt2;
4041
0
4142
(1 row)
4243

43-
SELECT count(*) FROM aqo_data;
44+
SELECT count(*) FROM aqo_data; -- Don't bother about false negatives because of trivial query plans
4445
count
4546
-------
4647
10
4748
(1 row)
4849

4950
DROP TABLE tt;
50-
SELECT aqo_cleanup();
51-
aqo_cleanup
52-
-------------
53-
(0,0)
51+
SELECT* FROMaqo_cleanup();
52+
nfs | nfss
53+
-----+------
54+
0 | 0
5455
(1 row)
5556

56-
SELECT count(*) FROM aqo_data; -- Shouldbe the same as above
57+
SELECT count(*) FROM aqo_data; -- Shouldreturn the same as previous call above
5758
count
5859
-------
5960
10
6061
(1 row)
6162

6263
DROP TABLE pt;
63-
SELECT aqo_cleanup();
64-
aqo_cleanup
65-
-------------
66-
(3,10)
64+
SELECT* FROMaqo_cleanup();
65+
nfs | nfss
66+
-----+------
67+
3 | 10
6768
(1 row)
6869

6970
SELECT count(*) FROM aqo_data; -- Should be 0
@@ -74,13 +75,11 @@ SELECT count(*) FROM aqo_data; -- Should be 0
7475

7576
SELECT query_text FROM aqo_queries aq LEFT JOIN aqo_query_texts aqt
7677
ON aq.queryid = aqt.queryid
77-
ORDER BY (md5(query_text)); -- TODO: should contain just one row
78-
query_text
79-
------------------------------------------
80-
SELECT count(*) FROM tt AS t1, tt AS t2;
78+
ORDER BY (md5(query_text)); -- The only the common class is returned
79+
query_text
80+
---------------------------------------
8181
COMMON feature space (do not delete!)
82-
SELECT count(*) FROM tt;
83-
(3 rows)
82+
(1 row)
8483

8584
-- Test learning on temporary table
8685
CREATE TABLE pt AS SELECT x AS x, (x % 10) AS y FROM generate_series(1,100) AS x;

‎expected/top_queries.out

Lines changed: 4 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ SET aqo.force_collect_stat = 'on';
1010
--
1111
CREATE TEMP TABLE ttt AS SELECT count(*) AS cnt FROM generate_series(1,10);
1212
CREATE TABLE ttp AS SELECT count(*) AS cnt FROM generate_series(1,10);
13-
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0;
13+
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; -- Ignore it
1414
cnt
1515
-----
1616
0
@@ -31,8 +31,7 @@ SELECT num FROM aqo_execution_time(false);
3131
num
3232
-----
3333
1
34-
2
35-
(2 rows)
34+
(1 row)
3635

3736
-- Without the AQO control queries with and without temp tables are logged.
3837
SELECT query_text,nexecs
@@ -41,9 +40,8 @@ WHERE ce.id = aqt.queryid
4140
ORDER BY (md5(query_text));
4241
query_text | nexecs
4342
------------------------------------------------------+--------
44-
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; | 1
4543
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0; | 1
46-
(2 rows)
44+
(1 row)
4745

4846
--
4947
-- num of query which uses the table t2 should be bigger than num of query which
@@ -93,10 +91,9 @@ ORDER BY (md5(query_text));
9391
query_text | nexecs
9492
------------------------------------------------------------------------------------------------+--------
9593
SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1
96-
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0; | 1
9794
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0; | 1
9895
SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1
99-
(4 rows)
96+
(3 rows)
10097

10198
SELECT 1 FROM aqo_reset();
10299
?column?

‎expected/unsupported.out

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -601,11 +601,23 @@ ORDER BY (md5(query_text),error) DESC;
601601
| ON q1.x = q2.x+1;
602602
(13 rows)
603603

604-
DROP TABLE t,t1 CASCADE;
605-
SELECT aqo_cleanup();
606-
aqo_cleanup
607-
-------------
608-
(12,42)
604+
DROP TABLE t,t1 CASCADE; -- delete all tables used in the test
605+
SELECT count(*) FROM aqo_data; -- Just to detect some changes in the logic. May some false positives really bother us here?
606+
count
607+
-------
608+
42
609+
(1 row)
610+
611+
SELECT * FROM aqo_cleanup();
612+
nfs | nfss
613+
-----+------
614+
12 | 42
615+
(1 row)
616+
617+
SELECT count(*) FROM aqo_data; -- No one row should be returned
618+
count
619+
-------
620+
0
609621
(1 row)
610622

611623
-- Look for any remaining queries in the ML storage.

‎preprocessing.c

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -406,7 +406,8 @@ typedef struct AQOPreWalkerCtx
406406

407407
/*
408408
* Examine a fully-parsed query, and return TRUE iff any relation underlying
409-
* the query is a system relation or no one relation touched by the query.
409+
* the query is a system relation or no one permanent (non-temporary) relation
410+
* touched by the query.
410411
*/
411412
staticbool
412413
isQueryUsingSystemRelation(Query*query)
@@ -497,11 +498,17 @@ isQueryUsingSystemRelation_walker(Node *node, void *context)
497498
boolis_catalog=IsCatalogRelation(rel);
498499
boolis_aqo_rel=IsAQORelation(rel);
499500

500-
table_close(rel,AccessShareLock);
501501
if (is_catalog||is_aqo_rel)
502+
{
503+
table_close(rel,AccessShareLock);
502504
return true;
505+
}
506+
507+
if (rel->rd_rel->relpersistence!=RELPERSISTENCE_TEMP)
508+
/* Plane non TEMP table */
509+
ctx->trivQuery= false;
503510

504-
ctx->trivQuery= false;
511+
table_close(rel,AccessShareLock);
505512
}
506513
elseif (rte->rtekind==RTE_FUNCTION)
507514
{

‎sql/aqo_learn.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -124,7 +124,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
124124
SELECTcount(*)FROM tmp1;
125125

126126
-- Remove data on some unneeded instances of tmp1 table.
127-
SELECT aqo_cleanup();
127+
SELECT*FROMaqo_cleanup();
128128

129129
-- Result of the query below should be empty
130130
SELECT*FROM aqo_query_texts aqt1, aqo_query_texts aqt2

‎sql/forced_stat_collection.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,4 +36,6 @@ ON aq.queryid = aqs.queryid;
3636

3737
SELECT query_textFROM aqo_query_textsORDER BY (md5(query_text));
3838

39+
DROPTABLE person;
40+
SELECT1FROM aqo_reset();-- Full remove of ML data before the end
3941
DROP EXTENSION aqo;

‎sql/temp_tables.sql

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,23 +8,23 @@ CREATE TABLE pt();
88
-- Ignore queries with the only temp tables
99
SELECTcount(*)FROM tt;
1010
SELECTcount(*)FROM ttAS t1, ttAS t2;
11-
SELECT*FROMaqo_data;
11+
SELECTquery_textFROMaqo_query_texts;-- Default row should be returned
1212

1313
-- Should be stored in the ML base
1414
SELECTcount(*)FROM pt;
1515
SELECTcount(*)FROM pt, tt;
1616
SELECTcount(*)FROM ptAS pt1, ttAS tt1, ttAS tt2, ptAS pt2;
17-
SELECTcount(*)FROM aqo_data;
17+
SELECTcount(*)FROM aqo_data;-- Don't bother about false negatives because of trivial query plans
1818

1919
DROPTABLE tt;
20-
SELECT aqo_cleanup();
21-
SELECTcount(*)FROM aqo_data;-- Shouldbe the same as above
20+
SELECT*FROMaqo_cleanup();
21+
SELECTcount(*)FROM aqo_data;-- Shouldreturn the same as previous call above
2222
DROPTABLE pt;
23-
SELECT aqo_cleanup();
23+
SELECT*FROMaqo_cleanup();
2424
SELECTcount(*)FROM aqo_data;-- Should be 0
2525
SELECT query_textFROM aqo_queries aqLEFT JOIN aqo_query_texts aqt
2626
ONaq.queryid=aqt.queryid
27-
ORDER BY (md5(query_text));--TODO: should contain just one row
27+
ORDER BY (md5(query_text));--The only the common class is returned
2828

2929
-- Test learning on temporary table
3030
CREATETABLEptASSELECT xAS x, (x %10)AS yFROM generate_series(1,100)AS x;

‎sql/top_queries.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ SET aqo.force_collect_stat = 'on';
1111
--
1212
CREATE TEMP TABLE tttASSELECTcount(*)AS cntFROM generate_series(1,10);
1313
CREATETABLEttpASSELECTcount(*)AS cntFROM generate_series(1,10);
14-
SELECTcount(*)AS cntFROM tttWHERE cnt %100=0;
14+
SELECTcount(*)AS cntFROM tttWHERE cnt %100=0;-- Ignore it
1515
SELECTcount(*)AS cntFROM ttpWHERE cnt %100=0;
1616
SELECT numFROM aqo_execution_time(true);-- Just for checking, return zero.
1717
SELECT numFROM aqo_execution_time(false);

‎sql/unsupported.sql

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -166,9 +166,11 @@ FROM aqo_cardinality_error(true) cef, aqo_query_texts aqt
166166
WHEREaqt.queryid=cef.id
167167
ORDER BY (md5(query_text),error)DESC;
168168

169-
DROPTABLE t,t1 CASCADE;
169+
DROPTABLE t,t1 CASCADE;-- delete all tables used in the test
170170

171-
SELECT aqo_cleanup();
171+
SELECTcount(*)FROM aqo_data;-- Just to detect some changes in the logic. May some false positives really bother us here?
172+
SELECT*FROM aqo_cleanup();
173+
SELECTcount(*)FROM aqo_data;-- No one row should be returned
172174

173175
-- Look for any remaining queries in the ML storage.
174176
SELECT to_char(error,'9.99EEEE')::textAS error, query_text

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp