提交: b6ebe309a92328110bc66786476b29879ee1272f - amalsyah12 (git)

firtst release

Commit MetaInfo

• 修訂: b6ebe309a92328110bc66786476b29879ee1272f (tree)
• 時間: 2018-12-05 17:31:20
• 作者: Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
• Commiter: Kyotaro Horiguchi

Log Message

Don't reset plpgsql nest level at every command end

Clean up code for global exit is forgetting about loops that run
hinted commands multiple times. This leads to pg_hint_plan's failure
to affect after the first execution of the inner commands.
The hint below worked only for the first time.

FOR outer IN EXECUTE 'outer-command' LOOP

FOR inner IN EXECUTE '/*+ hint */ inner-command' LOOP

<something>

END LOOP;

END LOOP;

Change Summary

• modified: expected/pg_hint_plan.out
• modified: pg_hint_plan.c
• modified: sql/pg_hint_plan.sql

差異

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -8178,6 +8178,185 @@ error hint:
 
 DROP FUNCTION testfunc();
 DROP EXTENSION pg_hint_plan;
+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
+DECLARE
+  rows int;
+BEGIN
+  rows = 1;
+  while rows > 0 LOOP
+   PERFORM pg_stat_reset();
+   PERFORM pg_sleep(0.5);
+   SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+-- Dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
+DECLARE c int;
+BEGIN
+  EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
+  	INTO c USING x;
+  RETURN c;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+vacuum analyze t1;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait 
+----------------------
+ 
+(1 row)
+
+SELECT dynsql1(9000);
+ dynsql1 
+---------
+    8999
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+ relname | seq_scan | idx_scan 
+---------+----------+----------
+ t1      | t        | f
+(1 row)
+
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait 
+----------------------
+ 
+(1 row)
+
+SELECT dynsql1(9000);
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1"
+PL/pgSQL function dynsql1(integer) line 4 at EXECUTE
+ dynsql1 
+---------
+    8999
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+ relname | seq_scan | idx_scan 
+---------+----------+----------
+ t1      | f        | t
+(1 row)
+
+-- Looped dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
+DECLARE
+  c text;
+  s int;
+BEGIN
+  r := 0;
+  FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
+    FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
+      r := r + s;
+    END LOOP;
+  END LOOP;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait 
+----------------------
+ 
+(1 row)
+
+SELECT dynsql2(9000);
+ dynsql2 
+---------
+    9900
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+-- one of the index scans happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+ relname | seq_scan | idx_scan 
+---------+----------+----------
+ p1_c1   |        1 |        0
+ p1_c2   |        1 |        1
+(2 rows)
+
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+ reset_stats_and_wait 
+----------------------
+ 
+(1 row)
+
+SELECT dynsql2(9000);
+LOG:  available indexes for IndexScan(p1_c1): p1_c1_pkey
+CONTEXT:  SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG:  available indexes for IndexScan(p1_c1_c1): p1_c1_c1_pkey
+CONTEXT:  SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG:  available indexes for IndexScan(p1_c1_c2): p1_c1_c2_pkey
+CONTEXT:  SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(p1_c1 p1_c1_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG:  available indexes for IndexScan(p1_c2): p1_c2_pkey
+CONTEXT:  SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(p1_c2 p1_c2_pkey)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000"
+PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement
+ dynsql2 
+---------
+    9900
+(1 row)
+
+SELECT pg_sleep(1);
+ pg_sleep 
+----------
+ 
+(1 row)
+
+-- the index scan happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+ relname | seq_scan | idx_scan 
+---------+----------+----------
+ p1_c1   |        0 |        1
+ p1_c2   |        0 |        2
+(2 rows)
+
 --
 -- Rows hint tests
 --

--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -4730,7 +4730,7 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase,
 								  bool isTopLevel,
 								  void *arg)
 {
-	if (phase != RESOURCE_RELEASE_AFTER_LOCKS)
+	if (!isTopLevel || phase != RESOURCE_RELEASE_AFTER_LOCKS)
 		return;
 	/* Cancel plpgsql nest level*/
 	plpgsql_recurse_level = 0;

--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -947,6 +947,67 @@ SELECT * FROM testfunc() LIMIT 1;
 DROP FUNCTION testfunc();
 DROP EXTENSION pg_hint_plan;
 
+CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$
+DECLARE
+  rows int;
+BEGIN
+  rows = 1;
+  while rows > 0 LOOP
+   PERFORM pg_stat_reset();
+   PERFORM pg_sleep(0.5);
+   SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$
+DECLARE c int;
+BEGIN
+  EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1'
+  	INTO c USING x;
+  RETURN c;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+vacuum analyze t1;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+SELECT dynsql1(9000);
+SELECT pg_sleep(1);
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+SELECT dynsql1(9000);
+SELECT pg_sleep(1);
+SELECT relname, seq_scan > 0 as seq_scan, idx_scan > 0 as idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND relname = 't1';
+
+-- Looped dynamic query in pl/pgsql
+CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$
+DECLARE
+  c text;
+  s int;
+BEGIN
+  r := 0;
+  FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP
+    FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP
+      r := r + s;
+    END LOOP;
+  END LOOP;
+END;
+$$ VOLATILE LANGUAGE plpgsql;
+SET pg_hint_plan.enable_hint = false;
+SELECT reset_stats_and_wait();
+SELECT dynsql2(9000);
+SELECT pg_sleep(1);
+-- one of the index scans happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+SET pg_hint_plan.enable_hint = true;
+SELECT reset_stats_and_wait();
+SELECT dynsql2(9000);
+SELECT pg_sleep(1);
+-- the index scan happened while planning.
+SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
+
 --
 -- Rows hint tests
 --