firtst release
修訂 | 72333a3a06005f7fbb46401691e66a13f32fc315 (tree) |
---|---|
時間 | 2018-12-05 17:32:21 |
作者 | Kyotaro Horiguchi <horiguchi.kyotaro@lab....> |
Commiter | Kyotaro Horiguchi |
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
END LOOP;
@@ -8164,6 +8164,185 @@ error hint: | ||
8164 | 8164 | |
8165 | 8165 | DROP FUNCTION testfunc(); |
8166 | 8166 | DROP EXTENSION pg_hint_plan; |
8167 | +CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$ | |
8168 | +DECLARE | |
8169 | + rows int; | |
8170 | +BEGIN | |
8171 | + rows = 1; | |
8172 | + while rows > 0 LOOP | |
8173 | + PERFORM pg_stat_reset(); | |
8174 | + PERFORM pg_sleep(0.5); | |
8175 | + SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows; | |
8176 | + END LOOP; | |
8177 | +END; | |
8178 | +$$ LANGUAGE plpgsql; | |
8179 | +-- Dynamic query in pl/pgsql | |
8180 | +CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$ | |
8181 | +DECLARE c int; | |
8182 | +BEGIN | |
8183 | + EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1' | |
8184 | + INTO c USING x; | |
8185 | + RETURN c; | |
8186 | +END; | |
8187 | +$$ VOLATILE LANGUAGE plpgsql; | |
8188 | +vacuum analyze t1; | |
8189 | +SET pg_hint_plan.enable_hint = false; | |
8190 | +SELECT reset_stats_and_wait(); | |
8191 | + reset_stats_and_wait | |
8192 | +---------------------- | |
8193 | + | |
8194 | +(1 row) | |
8195 | + | |
8196 | +SELECT dynsql1(9000); | |
8197 | + dynsql1 | |
8198 | +--------- | |
8199 | + 8999 | |
8200 | +(1 row) | |
8201 | + | |
8202 | +SELECT pg_sleep(1); | |
8203 | + pg_sleep | |
8204 | +---------- | |
8205 | + | |
8206 | +(1 row) | |
8207 | + | |
8208 | +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'; | |
8209 | + relname | seq_scan | idx_scan | |
8210 | +---------+----------+---------- | |
8211 | + t1 | t | f | |
8212 | +(1 row) | |
8213 | + | |
8214 | +SET pg_hint_plan.enable_hint = true; | |
8215 | +SELECT reset_stats_and_wait(); | |
8216 | + reset_stats_and_wait | |
8217 | +---------------------- | |
8218 | + | |
8219 | +(1 row) | |
8220 | + | |
8221 | +SELECT dynsql1(9000); | |
8222 | +LOG: pg_hint_plan: | |
8223 | +used hint: | |
8224 | +IndexScan(t1) | |
8225 | +not used hint: | |
8226 | +duplication hint: | |
8227 | +error hint: | |
8228 | + | |
8229 | +CONTEXT: SQL statement "/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1" | |
8230 | +PL/pgSQL function dynsql1(integer) line 4 at EXECUTE | |
8231 | + dynsql1 | |
8232 | +--------- | |
8233 | + 8999 | |
8234 | +(1 row) | |
8235 | + | |
8236 | +SELECT pg_sleep(1); | |
8237 | + pg_sleep | |
8238 | +---------- | |
8239 | + | |
8240 | +(1 row) | |
8241 | + | |
8242 | +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'; | |
8243 | + relname | seq_scan | idx_scan | |
8244 | +---------+----------+---------- | |
8245 | + t1 | f | t | |
8246 | +(1 row) | |
8247 | + | |
8248 | +-- Looped dynamic query in pl/pgsql | |
8249 | +CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$ | |
8250 | +DECLARE | |
8251 | + c text; | |
8252 | + s int; | |
8253 | +BEGIN | |
8254 | + r := 0; | |
8255 | + FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP | |
8256 | + FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP | |
8257 | + r := r + s; | |
8258 | + END LOOP; | |
8259 | + END LOOP; | |
8260 | +END; | |
8261 | +$$ VOLATILE LANGUAGE plpgsql; | |
8262 | +SET pg_hint_plan.enable_hint = false; | |
8263 | +SELECT reset_stats_and_wait(); | |
8264 | + reset_stats_and_wait | |
8265 | +---------------------- | |
8266 | + | |
8267 | +(1 row) | |
8268 | + | |
8269 | +SELECT dynsql2(9000); | |
8270 | + dynsql2 | |
8271 | +--------- | |
8272 | + 9900 | |
8273 | +(1 row) | |
8274 | + | |
8275 | +SELECT pg_sleep(1); | |
8276 | + pg_sleep | |
8277 | +---------- | |
8278 | + | |
8279 | +(1 row) | |
8280 | + | |
8281 | +-- one of the index scans happened while planning. | |
8282 | +SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2'); | |
8283 | + relname | seq_scan | idx_scan | |
8284 | +---------+----------+---------- | |
8285 | + p1_c1 | 1 | 0 | |
8286 | + p1_c2 | 1 | 1 | |
8287 | +(2 rows) | |
8288 | + | |
8289 | +SET pg_hint_plan.enable_hint = true; | |
8290 | +SELECT reset_stats_and_wait(); | |
8291 | + reset_stats_and_wait | |
8292 | +---------------------- | |
8293 | + | |
8294 | +(1 row) | |
8295 | + | |
8296 | +SELECT dynsql2(9000); | |
8297 | +LOG: available indexes for IndexScan(p1_c1): p1_c1_pkey | |
8298 | +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" | |
8299 | +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement | |
8300 | +LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_pkey | |
8301 | +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" | |
8302 | +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement | |
8303 | +LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_pkey | |
8304 | +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" | |
8305 | +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement | |
8306 | +LOG: pg_hint_plan: | |
8307 | +used hint: | |
8308 | +IndexScan(p1_c1 p1_c1_pkey) | |
8309 | +not used hint: | |
8310 | +duplication hint: | |
8311 | +error hint: | |
8312 | + | |
8313 | +CONTEXT: SQL statement "/*+ IndexScan(p1_c1 p1_c1_pkey) */ SELECT sum(val) FROM p1_c1 WHERE id < 9000" | |
8314 | +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement | |
8315 | +LOG: available indexes for IndexScan(p1_c2): p1_c2_pkey | |
8316 | +CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000" | |
8317 | +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement | |
8318 | +LOG: pg_hint_plan: | |
8319 | +used hint: | |
8320 | +IndexScan(p1_c2 p1_c2_pkey) | |
8321 | +not used hint: | |
8322 | +duplication hint: | |
8323 | +error hint: | |
8324 | + | |
8325 | +CONTEXT: SQL statement "/*+ IndexScan(p1_c2 p1_c2_pkey) */ SELECT sum(val) FROM p1_c2 WHERE id < 9000" | |
8326 | +PL/pgSQL function dynsql2(integer) line 8 at FOR over EXECUTE statement | |
8327 | + dynsql2 | |
8328 | +--------- | |
8329 | + 9900 | |
8330 | +(1 row) | |
8331 | + | |
8332 | +SELECT pg_sleep(1); | |
8333 | + pg_sleep | |
8334 | +---------- | |
8335 | + | |
8336 | +(1 row) | |
8337 | + | |
8338 | +-- the index scan happened while planning. | |
8339 | +SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2'); | |
8340 | + relname | seq_scan | idx_scan | |
8341 | +---------+----------+---------- | |
8342 | + p1_c1 | 0 | 1 | |
8343 | + p1_c2 | 0 | 2 | |
8344 | +(2 rows) | |
8345 | + | |
8167 | 8346 | -- |
8168 | 8347 | -- Rows hint tests |
8169 | 8348 | -- |
@@ -4126,7 +4126,7 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase, | ||
4126 | 4126 | bool isTopLevel, |
4127 | 4127 | void *arg) |
4128 | 4128 | { |
4129 | - if (phase != RESOURCE_RELEASE_AFTER_LOCKS) | |
4129 | + if (!isTopLevel || phase != RESOURCE_RELEASE_AFTER_LOCKS) | |
4130 | 4130 | return; |
4131 | 4131 | /* Cancel plpgsql nest level*/ |
4132 | 4132 | plpgsql_recurse_level = 0; |
@@ -942,6 +942,67 @@ SELECT * FROM testfunc() LIMIT 1; | ||
942 | 942 | DROP FUNCTION testfunc(); |
943 | 943 | DROP EXTENSION pg_hint_plan; |
944 | 944 | |
945 | +CREATE FUNCTION reset_stats_and_wait() RETURNS void AS $$ | |
946 | +DECLARE | |
947 | + rows int; | |
948 | +BEGIN | |
949 | + rows = 1; | |
950 | + while rows > 0 LOOP | |
951 | + PERFORM pg_stat_reset(); | |
952 | + PERFORM pg_sleep(0.5); | |
953 | + SELECT sum(seq_scan + idx_scan) from pg_stat_user_tables into rows; | |
954 | + END LOOP; | |
955 | +END; | |
956 | +$$ LANGUAGE plpgsql; | |
957 | + | |
958 | +-- Dynamic query in pl/pgsql | |
959 | +CREATE OR REPLACE FUNCTION dynsql1(x int) RETURNS int AS $$ | |
960 | +DECLARE c int; | |
961 | +BEGIN | |
962 | + EXECUTE '/*+ IndexScan(t1) */ SELECT count(*) FROM t1 WHERE id < $1' | |
963 | + INTO c USING x; | |
964 | + RETURN c; | |
965 | +END; | |
966 | +$$ VOLATILE LANGUAGE plpgsql; | |
967 | +vacuum analyze t1; | |
968 | +SET pg_hint_plan.enable_hint = false; | |
969 | +SELECT reset_stats_and_wait(); | |
970 | +SELECT dynsql1(9000); | |
971 | +SELECT pg_sleep(1); | |
972 | +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'; | |
973 | +SET pg_hint_plan.enable_hint = true; | |
974 | +SELECT reset_stats_and_wait(); | |
975 | +SELECT dynsql1(9000); | |
976 | +SELECT pg_sleep(1); | |
977 | +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'; | |
978 | + | |
979 | +-- Looped dynamic query in pl/pgsql | |
980 | +CREATE OR REPLACE FUNCTION dynsql2(x int, OUT r int) AS $$ | |
981 | +DECLARE | |
982 | + c text; | |
983 | + s int; | |
984 | +BEGIN | |
985 | + r := 0; | |
986 | + FOR c IN SELECT f.f FROM (VALUES ('p1_c1'), ('p1_c2')) f(f) LOOP | |
987 | + FOR s IN EXECUTE '/*+ IndexScan(' || c || ' ' || c || '_pkey) */ SELECT sum(val) FROM ' || c || ' WHERE id < ' || x LOOP | |
988 | + r := r + s; | |
989 | + END LOOP; | |
990 | + END LOOP; | |
991 | +END; | |
992 | +$$ VOLATILE LANGUAGE plpgsql; | |
993 | +SET pg_hint_plan.enable_hint = false; | |
994 | +SELECT reset_stats_and_wait(); | |
995 | +SELECT dynsql2(9000); | |
996 | +SELECT pg_sleep(1); | |
997 | +-- one of the index scans happened while planning. | |
998 | +SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2'); | |
999 | +SET pg_hint_plan.enable_hint = true; | |
1000 | +SELECT reset_stats_and_wait(); | |
1001 | +SELECT dynsql2(9000); | |
1002 | +SELECT pg_sleep(1); | |
1003 | +-- the index scan happened while planning. | |
1004 | +SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2'); | |
1005 | + | |
945 | 1006 | -- |
946 | 1007 | -- Rows hint tests |
947 | 1008 | -- |