1 -- ut-T: tests for table hints
2 -- This test is focusing on hint retrieval from table
5 SET pg_hint_plan.enable_hint TO on;
6 SET pg_hint_plan.debug_print TO on;
7 SET client_min_messages TO LOG;
8 SET search_path TO public;
10 -- test for get_query_string
11 INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
12 INSERT INTO hint_plan.hints VALUES(DEFAULT,'PREPARE p1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
13 INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
14 INSERT INTO hint_plan.hints VALUES(DEFAULT,'EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = ?;', '', 'SeqScan(t1)');
16 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
18 -- These queries uses IndexScan without hints
19 SET pg_hint_plan.enable_hint_table to off;
20 EXPLAIN SELECT * FROM t1 WHERE id = 100;
21 EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
22 EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
26 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
27 EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
30 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
32 -- Forced to use SeqScan by table hints
33 SET pg_hint_plan.enable_hint_table to on;
34 EXPLAIN SELECT * FROM t1 WHERE id = 100;
35 EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1 WHERE id = 100;
36 EXPLAIN CREATE TABLE ct1 AS SELECT * FROM t1 WHERE id = 100;
39 PREPARE p1 AS SELECT * FROM t1 WHERE id = 100;
40 EXPLAIN CREATE TABLE ct1 AS EXECUTE p1;
44 -- Check proper calling to generate_normalized_query
47 SET pg_hint_plan.enable_hint_table to off;
48 DELETE FROM hint_plan.hints;