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

Commit21bf37e

Browse files
Andrey Kazarinovdanolivo
Andrey Kazarinov
authored andcommitted
[PGPRO-6374] Review-related fixes
1 parente810d1d commit21bf37e

File tree

7 files changed

+191
-88
lines changed

7 files changed

+191
-88
lines changed

‎aqo--1.0--1.1.sql

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,13 @@
1-
ALTERTABLE aqo_query_texts ALTER COLUMN query_text TYPEtext;
1+
ALTERTABLEpublic.aqo_query_texts ALTER COLUMN query_text TYPEtext;
22

33

4-
DROPINDEX aqo_queries_query_hash_idx CASCADE;
5-
DROPINDEX aqo_query_texts_query_hash_idx CASCADE;
6-
DROPINDEX aqo_query_stat_idx CASCADE;
7-
DROPINDEX aqo_fss_access_idx CASCADE;
4+
DROPINDEXpublic.aqo_queries_query_hash_idx CASCADE;
5+
DROPINDEXpublic.aqo_query_texts_query_hash_idx CASCADE;
6+
DROPINDEXpublic.aqo_query_stat_idx CASCADE;
7+
DROPINDEXpublic.aqo_fss_access_idx CASCADE;
88

99
CREATEUNIQUE INDEXaqo_fss_access_idx
10-
ON aqo_data (fspace_hash, fsspace_hash);
10+
ONpublic.aqo_data (fspace_hash, fsspace_hash);
1111

1212

1313
CREATE OR REPLACEFUNCTIONaqo_migrate_to_1_1_get_pk(rel regclass) RETURNS regclassAS $$
@@ -28,15 +28,15 @@ $$ LANGUAGE plpgsql;
2828
DO $$
2929
BEGIN
3030
EXECUTEpg_catalog.format('ALTER TABLE %s RENAME to %s',
31-
aqo_migrate_to_1_1_get_pk('aqo_queries'),
31+
aqo_migrate_to_1_1_get_pk('public.aqo_queries'),
3232
'aqo_queries_query_hash_idx');
3333

3434
EXECUTEpg_catalog.format('ALTER TABLE %s RENAME to %s',
35-
aqo_migrate_to_1_1_get_pk('aqo_query_texts'),
35+
aqo_migrate_to_1_1_get_pk('public.aqo_query_texts'),
3636
'aqo_query_texts_query_hash_idx');
3737

3838
EXECUTEpg_catalog.format('ALTER TABLE %s RENAME to %s',
39-
aqo_migrate_to_1_1_get_pk('aqo_query_stat'),
39+
aqo_migrate_to_1_1_get_pk('public.aqo_query_stat'),
4040
'aqo_query_stat_idx');
4141
END
4242
$$;

‎aqo--1.0.sql

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,21 +1,21 @@
11
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
22
\echo Use"CREATE EXTENSION aqo" to load this file. \quit
33

4-
CREATETABLEaqo_queries (
4+
CREATETABLEpublic.aqo_queries (
55
query_hashbigintPRIMARY KEY,
66
learn_aqobooleanNOT NULL,
77
use_aqobooleanNOT NULL,
88
fspace_hashbigintNOT NULL,
99
auto_tuningbooleanNOT NULL
1010
);
1111

12-
CREATETABLEaqo_query_texts (
13-
query_hashbigintPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
12+
CREATETABLEpublic.aqo_query_texts (
13+
query_hashbigintPRIMARY KEYREFERENCESpublic.aqo_queriesON DELETE CASCADE,
1414
query_textvarcharNOT NULL
1515
);
1616

17-
CREATETABLEaqo_query_stat (
18-
query_hashbigintPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
17+
CREATETABLEpublic.aqo_query_stat (
18+
query_hashbigintPRIMARY KEYREFERENCESpublic.aqo_queriesON DELETE CASCADE,
1919
execution_time_with_aqodouble precision[],
2020
execution_time_without_aqodouble precision[],
2121
planning_time_with_aqodouble precision[],
@@ -26,27 +26,27 @@ CREATE TABLE aqo_query_stat (
2626
executions_without_aqobigint
2727
);
2828

29-
CREATETABLEaqo_data (
30-
fspace_hashbigintNOT NULLREFERENCES aqo_queriesON DELETE CASCADE,
29+
CREATETABLEpublic.aqo_data (
30+
fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,
3131
fsspace_hashintNOT NULL,
3232
nfeaturesintNOT NULL,
3333
featuresdouble precision[][],
3434
targetsdouble precision[],
3535
UNIQUE (fspace_hash, fsspace_hash)
3636
);
3737

38-
CREATEINDEXaqo_queries_query_hash_idxON aqo_queries (query_hash);
39-
CREATEINDEXaqo_query_texts_query_hash_idxON aqo_query_texts (query_hash);
40-
CREATEINDEXaqo_query_stat_idxON aqo_query_stat (query_hash);
41-
CREATEINDEXaqo_fss_access_idxON aqo_data (fspace_hash, fsspace_hash);
38+
CREATEINDEXaqo_queries_query_hash_idxONpublic.aqo_queries (query_hash);
39+
CREATEINDEXaqo_query_texts_query_hash_idxONpublic.aqo_query_texts (query_hash);
40+
CREATEINDEXaqo_query_stat_idxONpublic.aqo_query_stat (query_hash);
41+
CREATEINDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);
4242

43-
INSERT INTO aqo_queriesVALUES (0, false, false,0, false);
44-
INSERT INTO aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
43+
INSERT INTOpublic.aqo_queriesVALUES (0, false, false,0, false);
44+
INSERT INTOpublic.aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
4545
-- a virtual query for COMMON feature space
4646

4747
CREATEFUNCTIONinvalidate_deactivated_queries_cache() RETURNS trigger
4848
AS'MODULE_PATHNAME' LANGUAGE C;
4949

5050
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
51-
ON aqo_queries FOR EACH STATEMENT
51+
ONpublic.aqo_queries FOR EACH STATEMENT
5252
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();

‎aqo--1.1--1.2.sql

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -14,8 +14,8 @@ $$ LANGUAGE plpgsql;
1414
DO $$
1515
BEGIN
1616
EXECUTEpg_catalog.format(
17-
'ALTER TABLE aqo_data DROP CONSTRAINT %s',
18-
aqo_migrate_to_1_2_get_pk('aqo_data'::regclass),
17+
'ALTER TABLEpublic.aqo_data DROP CONSTRAINT %s',
18+
aqo_migrate_to_1_2_get_pk('public.aqo_data'::regclass),
1919
'aqo_queries_query_hash_idx');
2020
END
2121
$$;
@@ -28,7 +28,7 @@ DROP FUNCTION aqo_migrate_to_1_2_get_pk(regclass);
2828
--
2929

3030
-- Show query state at the AQO knowledge base
31-
CREATE OR REPLACEFUNCTIONaqo_status(hashbigint)
31+
CREATE OR REPLACEFUNCTIONpublic.aqo_status(hashbigint)
3232
RETURNS TABLE (
3333
"learn"BOOL,
3434
"use aqo"BOOL,
@@ -49,58 +49,58 @@ SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,
4949
to_char(execution_time_with_aqo[n3],'9.99EEEE'),
5050
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
5151
executions_with_aqo
52-
FROM aqo_queries aq, aqo_query_stat aqs,
52+
FROMpublic.aqo_queries aq,public.aqo_query_stat aqs,
5353
(SELECT array_length(n1,1)AS n1, array_length(n2,1)AS n2,
5454
array_length(n3,1)AS n3, array_length(n4,1)AS n4
5555
FROM
5656
(SELECT cardinality_error_with_aqoAS n1,
5757
cardinality_error_without_aqoAS n2,
5858
execution_time_with_aqoAS n3,
5959
execution_time_without_aqoAS n4
60-
FROM aqo_query_stat aqsWHERE
60+
FROMpublic.aqo_query_stat aqsWHERE
6161
aqs.query_hash= $1)AS al)AS q
6262
WHERE (aqs.query_hash=aq.query_hash)AND
6363
aqs.query_hash= $1;
6464
$func$ LANGUAGE SQL;
6565

66-
CREATE OR REPLACEFUNCTIONaqo_enable_query(hashbigint)
66+
CREATE OR REPLACEFUNCTIONpublic.aqo_enable_query(hashbigint)
6767
RETURNS VOID
6868
AS $func$
69-
UPDATE aqo_queriesSET
69+
UPDATEpublic.aqo_queriesSET
7070
learn_aqo='true',
7171
use_aqo='true'
7272
WHERE query_hash= $1;
7373
$func$ LANGUAGE SQL;
7474

75-
CREATE OR REPLACEFUNCTIONaqo_disable_query(hashbigint)
75+
CREATE OR REPLACEFUNCTIONpublic.aqo_disable_query(hashbigint)
7676
RETURNS VOID
7777
AS $func$
78-
UPDATE aqo_queriesSET
78+
UPDATEpublic.aqo_queriesSET
7979
learn_aqo='false',
8080
use_aqo='false',
8181
auto_tuning='false'
8282
WHERE query_hash= $1;
8383
$func$ LANGUAGE SQL;
8484

85-
CREATE OR REPLACEFUNCTIONaqo_clear_hist(hashbigint)
85+
CREATE OR REPLACEFUNCTIONpublic.aqo_clear_hist(hashbigint)
8686
RETURNS VOID
8787
AS $func$
88-
DELETEFROM aqo_dataWHERE fspace_hash=$1;
88+
DELETEFROMpublic.aqo_dataWHERE fspace_hash=$1;
8989
$func$ LANGUAGE SQL;
9090

9191
-- Show queries that contains 'Never executed' nodes at the plan.
92-
CREATE OR REPLACEFUNCTIONaqo_ne_queries()
92+
CREATE OR REPLACEFUNCTIONpublic.aqo_ne_queries()
9393
RETURNS SETOFint
9494
AS $func$
95-
SELECT query_hashFROM aqo_query_stat aqs
95+
SELECT query_hashFROMpublic.aqo_query_stat aqs
9696
WHERE-1= ANY (cardinality_error_with_aqo::double precision[]);
9797
$func$ LANGUAGE SQL;
9898

99-
CREATE OR REPLACEFUNCTIONaqo_drop(hashbigint)
99+
CREATE OR REPLACEFUNCTIONpublic.aqo_drop(hashbigint)
100100
RETURNS VOID
101101
AS $func$
102-
DELETEFROM aqo_queries aqWHERE (aq.query_hash= $1);
103-
DELETEFROM aqo_data adWHERE (ad.fspace_hash= $1);
104-
DELETEFROM aqo_query_stat aqWHERE (aq.query_hash= $1);
105-
DELETEFROM aqo_query_texts aqWHERE (aq.query_hash= $1);
102+
DELETEFROMpublic.aqo_queries aqWHERE (aq.query_hash= $1);
103+
DELETEFROMpublic.aqo_data adWHERE (ad.fspace_hash= $1);
104+
DELETEFROMpublic.aqo_query_stat aqWHERE (aq.query_hash= $1);
105+
DELETEFROMpublic.aqo_query_texts aqWHERE (aq.query_hash= $1);
106106
$func$ LANGUAGE SQL;

‎aqo--1.2--1.3.sql

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
1-
ALTERTABLE aqo_data ADD COLUMN oidstext [] DEFAULTNULL;
1+
ALTERTABLEpublic.aqo_data ADD COLUMN oidstext [] DEFAULTNULL;
22

33
--
44
-- Remove data, related to previously dropped tables, from the AQO tables.
55
--
6-
CREATE OR REPLACEFUNCTIONclean_aqo_data() RETURNS voidAS $$
6+
CREATE OR REPLACEFUNCTIONpublic.clean_aqo_data() RETURNS voidAS $$
77
DECLARE
88
aqo_data_row aqo_data%ROWTYPE;
99
aqo_queries_row aqo_queries%ROWTYPE;
@@ -29,7 +29,7 @@ BEGIN
2929
END LOOP;
3030
END IF;
3131

32-
FOR aqo_queries_rowIN (SELECT*FROM aqo_queries)
32+
FOR aqo_queries_rowIN (SELECT*FROMpublic.aqo_queries)
3333
LOOP
3434
IF (delete_row= trueAND fspace_hash_var<>0AND
3535
fspace_hash_var=aqo_queries_row.fspace_hashAND
@@ -87,7 +87,7 @@ $$ LANGUAGE plpgsql;
8787
--
8888
-- Top of queries with the highest value of execution time.
8989
--
90-
CREATE OR REPLACEFUNCTIONtop_time_queries(nint)
90+
CREATE OR REPLACEFUNCTIONpublic.top_time_queries(nint)
9191
RETURNS TABLE(numbigint,
9292
fspace_hashbigint,
9393
query_hashbigint,
@@ -103,7 +103,7 @@ BEGIN
103103
aqo_queries.query_hash,
104104
to_char(array_avg(execution_time_without_aqo),'9.99EEEE')::float,
105105
to_char(array_mse(execution_time_without_aqo),'9.99EEEE')::float
106-
FROM aqo_queriesINNER JOIN aqo_query_stat
106+
FROMpublic.aqo_queriesINNER JOIN aqo_query_stat
107107
ONaqo_queries.query_hash=aqo_query_stat.query_hash
108108
GROUP BY (execution_time_without_aqo,aqo_queries.fspace_hash,aqo_queries.query_hash)
109109
ORDER BY execution_timeDESCLIMIT n;
@@ -113,7 +113,7 @@ $$ LANGUAGE plpgsql;
113113
--
114114
-- Top of queries with largest value of total cardinality error.
115115
--
116-
CREATE OR REPLACEFUNCTIONtop_error_queries(nint)
116+
CREATE OR REPLACEFUNCTIONpublic.top_error_queries(nint)
117117
RETURNS TABLE(numbigint,
118118
fspace_hashbigint,
119119
query_hashbigint,
@@ -129,7 +129,7 @@ BEGIN
129129
aqo_queries.query_hash,
130130
to_char(array_avg(cardinality_error_without_aqo),'9.99EEEE')::float,
131131
to_char(array_mse(cardinality_error_without_aqo),'9.99EEEE')::float
132-
FROM aqo_queriesINNER JOIN aqo_query_stat
132+
FROMpublic.aqo_queriesINNER JOIN aqo_query_stat
133133
ONaqo_queries.query_hash=aqo_query_stat.query_hash
134134
GROUP BY (cardinality_error_without_aqo,aqo_queries.fspace_hash,aqo_queries.query_hash)
135135
ORDER BY errorDESCLIMIT n;

‎aqo--1.2.sql

Lines changed: 27 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,21 +1,21 @@
11
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
22
\echo Use"CREATE EXTENSION aqo" to load this file. \quit
33

4-
CREATETABLEaqo_queries (
4+
CREATETABLEpublic.aqo_queries (
55
query_hashbigintCONSTRAINT aqo_queries_query_hash_idxPRIMARY KEY,
66
learn_aqobooleanNOT NULL,
77
use_aqobooleanNOT NULL,
88
fspace_hashbigintNOT NULL,
99
auto_tuningbooleanNOT NULL
1010
);
1111

12-
CREATETABLEaqo_query_texts (
13-
query_hashbigintCONSTRAINT aqo_query_texts_query_hash_idxPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
12+
CREATETABLEpublic.aqo_query_texts (
13+
query_hashbigintCONSTRAINT aqo_query_texts_query_hash_idxPRIMARY KEYREFERENCESpublic.aqo_queriesON DELETE CASCADE,
1414
query_texttextNOT NULL
1515
);
1616

17-
CREATETABLEaqo_query_stat (
18-
query_hashbigintCONSTRAINT aqo_query_stat_idxPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
17+
CREATETABLEpublic.aqo_query_stat (
18+
query_hashbigintCONSTRAINT aqo_query_stat_idxPRIMARY KEYREFERENCESpublic.aqo_queriesON DELETE CASCADE,
1919
execution_time_with_aqodouble precision[],
2020
execution_time_without_aqodouble precision[],
2121
planning_time_with_aqodouble precision[],
@@ -26,33 +26,33 @@ CREATE TABLE aqo_query_stat (
2626
executions_without_aqobigint
2727
);
2828

29-
CREATETABLEaqo_data (
30-
fspace_hashbigintNOT NULLREFERENCES aqo_queriesON DELETE CASCADE,
29+
CREATETABLEpublic.aqo_data (
30+
fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,
3131
fsspace_hashintNOT NULL,
3232
nfeaturesintNOT NULL,
3333
featuresdouble precision[][],
3434
targetsdouble precision[]
3535
);
3636

37-
CREATEUNIQUE INDEXaqo_fss_access_idxON aqo_data (fspace_hash, fsspace_hash);
37+
CREATEUNIQUE INDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);
3838

39-
INSERT INTO aqo_queriesVALUES (0, false, false,0, false);
40-
INSERT INTO aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
39+
INSERT INTOpublic.aqo_queriesVALUES (0, false, false,0, false);
40+
INSERT INTOpublic.aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
4141
-- a virtual query for COMMON feature space
4242

4343
CREATEFUNCTIONinvalidate_deactivated_queries_cache() RETURNS trigger
4444
AS'MODULE_PATHNAME' LANGUAGE C;
4545

4646
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
47-
ON aqo_queries FOR EACH STATEMENT
47+
ONpublic.aqo_queries FOR EACH STATEMENT
4848
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();
4949

5050
--
5151
-- Service functions
5252
--
5353

5454
-- Show query state at the AQO knowledge base
55-
CREATEFUNCTIONaqo_status(hashbigint)
55+
CREATEFUNCTIONpublic.aqo_status(hashbigint)
5656
RETURNS TABLE (
5757
"learn"BOOL,
5858
"use aqo"BOOL,
@@ -73,58 +73,58 @@ SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,
7373
to_char(execution_time_with_aqo[n3],'9.99EEEE'),
7474
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
7575
executions_with_aqo
76-
FROM aqo_queries aq, aqo_query_stat aqs,
76+
FROMpublic.aqo_queries aq,public.aqo_query_stat aqs,
7777
(SELECT array_length(n1,1)AS n1, array_length(n2,1)AS n2,
7878
array_length(n3,1)AS n3, array_length(n4,1)AS n4
7979
FROM
8080
(SELECT cardinality_error_with_aqoAS n1,
8181
cardinality_error_without_aqoAS n2,
8282
execution_time_with_aqoAS n3,
8383
execution_time_without_aqoAS n4
84-
FROM aqo_query_stat aqsWHERE
84+
FROMpublic.aqo_query_stat aqsWHERE
8585
aqs.query_hash= $1)AS al)AS q
8686
WHERE (aqs.query_hash=aq.query_hash)AND
8787
aqs.query_hash= $1;
8888
$func$ LANGUAGE SQL;
8989

90-
CREATEFUNCTIONaqo_enable_query(hashbigint)
90+
CREATEFUNCTIONpublic.aqo_enable_query(hashbigint)
9191
RETURNS VOID
9292
AS $func$
93-
UPDATE aqo_queriesSET
93+
UPDATEpublic.aqo_queriesSET
9494
learn_aqo='true',
9595
use_aqo='true'
9696
WHERE query_hash= $1;
9797
$func$ LANGUAGE SQL;
9898

99-
CREATEFUNCTIONaqo_disable_query(hashbigint)
99+
CREATEFUNCTIONpublic.aqo_disable_query(hashbigint)
100100
RETURNS VOID
101101
AS $func$
102-
UPDATE aqo_queriesSET
102+
UPDATEpublic.aqo_queriesSET
103103
learn_aqo='false',
104104
use_aqo='false',
105105
auto_tuning='false'
106106
WHERE query_hash= $1;
107107
$func$ LANGUAGE SQL;
108108

109-
CREATEFUNCTIONaqo_clear_hist(hashbigint)
109+
CREATEFUNCTIONpublic.aqo_clear_hist(hashbigint)
110110
RETURNS VOID
111111
AS $func$
112-
DELETEFROM aqo_dataWHERE fspace_hash=$1;
112+
DELETEFROMpublic.aqo_dataWHERE fspace_hash=$1;
113113
$func$ LANGUAGE SQL;
114114

115115
-- Show queries that contains 'Never executed' nodes at the plan.
116-
CREATEFUNCTIONaqo_ne_queries()
116+
CREATEFUNCTIONpublic.aqo_ne_queries()
117117
RETURNS SETOFint
118118
AS $func$
119-
SELECT query_hashFROM aqo_query_stat aqs
119+
SELECT query_hashFROMpublic.aqo_query_stat aqs
120120
WHERE-1= ANY (cardinality_error_with_aqo::double precision[]);
121121
$func$ LANGUAGE SQL;
122122

123-
CREATEFUNCTIONaqo_drop(hashbigint)
123+
CREATEFUNCTIONpublic.aqo_drop(hashbigint)
124124
RETURNS VOID
125125
AS $func$
126-
DELETEFROM aqo_queries aqWHERE (aq.query_hash= $1);
127-
DELETEFROM aqo_data adWHERE (ad.fspace_hash= $1);
128-
DELETEFROM aqo_query_stat aqWHERE (aq.query_hash= $1);
129-
DELETEFROM aqo_query_texts aqWHERE (aq.query_hash= $1);
126+
DELETEFROMpublic.aqo_queries aqWHERE (aq.query_hash= $1);
127+
DELETEFROMpublic.aqo_data adWHERE (ad.fspace_hash= $1);
128+
DELETEFROMpublic.aqo_query_stat aqWHERE (aq.query_hash= $1);
129+
DELETEFROMpublic.aqo_query_texts aqWHERE (aq.query_hash= $1);
130130
$func$ LANGUAGE SQL;

0 commit comments

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