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

Commit922c510

Browse files
committed
Force create aqo-related database objects into the public schema.
Now aqo is not relocatable extension. It uses the public schema for service database objects. It is made for code simplification reason.A simple test on this property is included.Per report from Jim Finnerty.
1 parente51400c commit922c510

File tree

5 files changed

+106
-32
lines changed

5 files changed

+106
-32
lines changed

‎Makefile‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,13 @@ OBJS = aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o \
88
hash.o machine_learning.o path_utils.o postprocessing.o preprocessing.o\
99
selectivity_cache.o storage.o utils.o$(WIN32RES)
1010

11-
REGRESS = aqo_disabled aqo_controlled aqo_intelligent aqo_forced aqo_learn
11+
REGRESS =aqo_disabled\
12+
aqo_controlled\
13+
aqo_intelligent\
14+
aqo_forced\
15+
aqo_learn\
16+
schema
17+
1218
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
1319

1420
DATA = aqo--1.0.sql aqo--1.0--1.1.sql

‎aqo--1.0--1.1.sql‎

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

33

4-
DROPINDEXaqo_query_texts_query_hash_idx CASCADE;
5-
DROPINDEXaqo_queries_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

9-
CREATEUNIQUE INDEXaqo_fss_access_idxON aqo_data (fspace_hash, fsspace_hash);
9+
CREATEUNIQUE INDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);
1010

1111

1212
CREATE OR REPLACEFUNCTIONaqo_migrate_to_1_1_get_pk(rel regclass) RETURNS regclassAS $$
@@ -28,15 +28,15 @@ $$ LANGUAGE plpgsql;
2828
DO $$
2929
BEGIN
3030
EXECUTE 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
EXECUTE 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
EXECUTE 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: 22 additions & 22 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_hashintPRIMARY KEY,
66
learn_aqobooleanNOT NULL,
77
use_aqobooleanNOT NULL,
88
fspace_hashintNOT NULL,
99
auto_tuningbooleanNOT NULL
1010
);
1111

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

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

29-
CREATETABLEaqo_data (
30-
fspace_hashintNOT NULLREFERENCES aqo_queriesON DELETE CASCADE,
29+
CREATETABLEpublic.aqo_data (
30+
fspace_hashintNOT 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-
ALTERTABLE aqo_data ALTER COLUMN featuresSET STORAGE MAIN;
44-
ALTERTABLE aqo_data ALTER COLUMN targetsSET STORAGE MAIN;
45-
ALTERTABLE aqo_query_stat
43+
ALTERTABLEpublic.aqo_data ALTER COLUMN featuresSET STORAGE MAIN;
44+
ALTERTABLEpublic.aqo_data ALTER COLUMN targetsSET STORAGE MAIN;
45+
ALTERTABLEpublic.aqo_query_stat
4646
ALTER COLUMN execution_time_with_aqoSET STORAGE MAIN;
47-
ALTERTABLE aqo_query_stat
47+
ALTERTABLEpublic.aqo_query_stat
4848
ALTER COLUMN execution_time_without_aqoSET STORAGE MAIN;
49-
ALTERTABLE aqo_query_stat
49+
ALTERTABLEpublic.aqo_query_stat
5050
ALTER COLUMN planning_time_with_aqoSET STORAGE MAIN;
51-
ALTERTABLE aqo_query_stat
51+
ALTERTABLEpublic.aqo_query_stat
5252
ALTER COLUMN planning_time_without_aqoSET STORAGE MAIN;
53-
ALTERTABLE aqo_query_stat
53+
ALTERTABLEpublic.aqo_query_stat
5454
ALTER COLUMN cardinality_error_without_aqoSET STORAGE MAIN;
55-
ALTERTABLE aqo_query_stat
55+
ALTERTABLEpublic.aqo_query_stat
5656
ALTER COLUMN cardinality_error_with_aqoSET STORAGE MAIN;
5757

58-
INSERT INTO aqo_queriesVALUES (0, false, false,0, false);
59-
INSERT INTO aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
58+
INSERT INTOpublic.aqo_queriesVALUES (0, false, false,0, false);
59+
INSERT INTOpublic.aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
6060
-- a virtual query for COMMON feature space
6161

6262
CREATEFUNCTIONinvalidate_deactivated_queries_cache() RETURNS trigger
6363
AS'MODULE_PATHNAME' LANGUAGE C;
6464

6565
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
66-
ON aqo_queries FOR EACH STATEMENT
66+
ONpublic.aqo_queries FOR EACH STATEMENT
6767
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();

‎expected/schema.out‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
DROP EXTENSION IF EXISTS aqo CASCADE;
2+
NOTICE: extension "aqo" does not exist, skipping
3+
DROP SCHEMA IF EXISTS test CASCADE;
4+
NOTICE: schema "test" does not exist, skipping
5+
-- Check Zero-schema path behaviour
6+
CREATE SCHEMA IF NOT EXISTS test;
7+
SET search_path TO test;
8+
DROP SCHEMA IF EXISTS test CASCADE;
9+
CREATE EXTENSION aqo; -- fail
10+
ERROR: no schema has been selected to create in
11+
-- Check default schema switching after AQO initialization
12+
CREATE SCHEMA IF NOT EXISTS test1;
13+
SET search_path TO test1, public;
14+
CREATE EXTENSION aqo;
15+
SET aqo.mode = 'intelligent';
16+
CREATE TABLE test (id SERIAL, data TEXT);
17+
INSERT INTO test (data) VALUES ('string');
18+
SELECT * FROM test;
19+
id | data
20+
----+--------
21+
1 | string
22+
(1 row)
23+
24+
SELECT * FROM public.aqo_query_texts;
25+
query_hash | query_text
26+
------------+--------------------------------------------
27+
0 | COMMON feature space (do not delete!)
28+
1136691690 | INSERT INTO test (data) VALUES ('string');
29+
868705076 | SELECT * FROM test;
30+
826229959 | SELECT * FROM public.aqo_query_texts;
31+
(4 rows)
32+
33+
SELECT * FROM public.aqo_queries;
34+
query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning
35+
------------+-----------+---------+-------------+-------------
36+
0 | f | f | 0 | f
37+
1136691690 | t | f | 1136691690 | t
38+
868705076 | t | f | 868705076 | t
39+
826229959 | t | f | 826229959 | t
40+
2145866904 | t | f | 2145866904 | t
41+
(5 rows)
42+
43+
DROP SCHEMA IF EXISTS test1 CASCADE;
44+
NOTICE: drop cascades to 2 other objects
45+
DETAIL: drop cascades to extension aqo
46+
drop cascades to table test

‎sql/schema.sql‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
DROP EXTENSION IF EXISTS aqo CASCADE;
2+
DROPSCHEMA IF EXISTS test CASCADE;
3+
4+
-- Check Zero-schema path behaviour
5+
CREATESCHEMAIF NOT EXISTS test;
6+
SET search_path TO test;
7+
DROPSCHEMA IF EXISTS test CASCADE;
8+
CREATE EXTENSION aqo;-- fail
9+
10+
-- Check default schema switching after AQO initialization
11+
CREATESCHEMAIF NOT EXISTS test1;
12+
SET search_path TO test1, public;
13+
CREATE EXTENSION aqo;
14+
SETaqo.mode='intelligent';
15+
16+
CREATETABLEtest (idSERIAL, dataTEXT);
17+
INSERT INTO test (data)VALUES ('string');
18+
19+
SELECT*FROM test;
20+
SELECT*FROMpublic.aqo_query_texts;
21+
SELECT*FROMpublic.aqo_queries;
22+
DROPSCHEMA IF EXISTS test1 CASCADE;

0 commit comments

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