Remove aqo_reset_query until we realize it is necessary.

danolivo · danolivo · commit 60dc4e6c43ac · 2022-09-07T09:49:33.000+05:00
Now we are on a way to relocatability and this function must be rewrited or
removed. So far we haven't used it - maybe it have a bad design?
Also fix regression tests unstability - rows reordering issue.
diff --git a/aqo--1.4--1.5.sql b/aqo--1.4--1.5.sql
@@ -208,33 +208,6 @@ $$ LANGUAGE plpgsql;
 COMMENT ON FUNCTION aqo_cardinality_error(boolean) IS
 'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';
 
---
--- Remove all learning data for query with given ID.
--- Can be used in the case when user don't want to drop preferences and
--- accumulated statistics on a query class, but tries to re-learn AQO on this
--- class.
--- Returns a number of deleted rows in the aqo_data table.
---
-CREATE OR REPLACE FUNCTION aqo_reset_query(queryid_res bigint)
-RETURNS integer AS $$
-DECLARE
-  num integer;
-  lfs  bigint;
-BEGIN
-  IF (queryid_res = 0) THEN
-    raise WARNING '[AQO] Reset common feature space.'
-  END IF;
-
-  SELECT fs FROM aqo_queries WHERE queryid = queryid_res INTO lfs;
-  SELECT count(*) FROM aqo_data WHERE fs = lfs INTO num;
-  DELETE FROM aqo_data WHERE fs = lfs;
-  RETURN num;
-END;
-$$ LANGUAGE plpgsql;
-
-COMMENT ON FUNCTION aqo_reset_query(bigint) IS
-'Remove from AQO storage only learning data for given QueryId.';
-
 CREATE FUNCTION aqo_status(hash bigint)
 RETURNS TABLE (
 	"learn"			BOOL,
diff --git a/expected/forced_stat_collection.out b/expected/forced_stat_collection.out
@@ -40,7 +40,8 @@ SELECT * FROM aqo_data;
 
 SELECT learn_aqo,use_aqo,auto_tuning,cardinality_error_without_aqo ce,executions_without_aqo nex
 FROM aqo_queries AS aq JOIN aqo_query_stat AS aqs
-ON aq.queryid = aqs.queryid;
+ON aq.queryid = aqs.queryid
+ORDER BY (cardinality_error_without_aqo);
  learn_aqo | use_aqo | auto_tuning |          ce          | nex 
 -----------+---------+-------------+----------------------+-----
  f         | f       | f           | {0.8637762840285226} |   1
diff --git a/expected/gucs.out b/expected/gucs.out
@@ -62,12 +62,6 @@ SELECT obj_description('aqo_cleanup'::regproc::oid);
  Remove unneeded rows from the AQO ML storage
 (1 row)
 
-SELECT obj_description('aqo_reset_query'::regproc::oid);
-                        obj_description                        
----------------------------------------------------------------
- Remove from AQO storage only learning data for given QueryId.
-(1 row)
-
 SELECT obj_description('aqo_reset'::regproc::oid);
         obj_description         
 --------------------------------
@@ -102,13 +96,6 @@ SELECT obj_description('aqo_reset'::regproc::oid);
  public | aqo_cleanup | record           | OUT nfs integer, OUT nfss integer | func
 (1 row)
 
-\df aqo_reset_query
-                            List of functions
- Schema |      Name       | Result data type | Argument data types | Type 
---------+-----------------+------------------+---------------------+------
- public | aqo_reset_query | integer          | queryid_res bigint  | func
-(1 row)
-
 \df aqo_reset
                          List of functions
  Schema |   Name    | Result data type | Argument data types | Type 
diff --git a/expected/relocatable.out b/expected/relocatable.out
@@ -88,7 +88,8 @@ SELECT aqo_disable_query(id) FROM (
  
 (2 rows)
 
-SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries;
+SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries
+ORDER BY (learn_aqo, use_aqo, auto_tuning);
  learn_aqo | use_aqo | auto_tuning 
 -----------+---------+-------------
  f         | f       | f
@@ -104,7 +105,8 @@ SELECT aqo_enable_query(id) FROM (
  
 (2 rows)
 
-SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries;
+SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries
+ORDER BY (learn_aqo, use_aqo, auto_tuning);
  learn_aqo | use_aqo | auto_tuning 
 -----------+---------+-------------
  f         | f       | f
diff --git a/sql/forced_stat_collection.sql b/sql/forced_stat_collection.sql
@@ -32,7 +32,8 @@ SELECT * FROM aqo_data;
 
 SELECT learn_aqo,use_aqo,auto_tuning,cardinality_error_without_aqo ce,executions_without_aqo nex
 FROM aqo_queries AS aq JOIN aqo_query_stat AS aqs
-ON aq.queryid = aqs.queryid;
+ON aq.queryid = aqs.queryid
+ORDER BY (cardinality_error_without_aqo);
 
 SELECT query_text FROM aqo_query_texts ORDER BY (md5(query_text));
 
diff --git a/sql/gucs.sql b/sql/gucs.sql
@@ -21,14 +21,12 @@ SELECT obj_description('aqo_cardinality_error'::regproc::oid);
 SELECT obj_description('aqo_execution_time'::regproc::oid);
 SELECT obj_description('aqo_drop_class'::regproc::oid);
 SELECT obj_description('aqo_cleanup'::regproc::oid);
-SELECT obj_description('aqo_reset_query'::regproc::oid);
 SELECT obj_description('aqo_reset'::regproc::oid);
 
 \df aqo_cardinality_error
 \df aqo_execution_time
 \df aqo_drop_class
 \df aqo_cleanup
-\df aqo_reset_query
 \df aqo_reset
 
 -- Check stat reset
diff --git a/sql/relocatable.sql b/sql/relocatable.sql
@@ -41,10 +41,12 @@ ORDER BY (md5(query_text))
  */
 SELECT aqo_disable_query(id) FROM (
   SELECT queryid AS id FROM aqo_queries WHERE queryid <> 0) AS q1;
-SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries;
+SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries
+ORDER BY (learn_aqo, use_aqo, auto_tuning);
 SELECT aqo_enable_query(id) FROM (
   SELECT queryid AS id FROM aqo_queries WHERE queryid <> 0) AS q1;
-SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries;
+SELECT learn_aqo, use_aqo, auto_tuning FROM test.aqo_queries
+ORDER BY (learn_aqo, use_aqo, auto_tuning);
 
 RESET search_path;
 DROP TABLE test CASCADE;
