MOGDB/openGauss資料庫執行計劃快取/失效機制的測試

1.強制對臨時物件使用COMMIT而不是2PC

postgres=# SET enforce_two_phase_commit TO off;
SET

2.建立測試表並插入測試資料

postgres=# CREATE TEMP TABLE tab_test_plancache(q1 int8, q2 int8);
CREATE TABLE
postgres=# INSERT INTO tab_test_plancache VALUES('  123   ','  456');
INSERT 0 1
postgres=# INSERT INTO tab_test_plancache VALUES('123   ','4567890123456789');
INSERT 0 1
postgres=# INSERT INTO tab_test_plancache VALUES('4567890123456789','123');
INSERT 0 1
postgres=# INSERT INTO tab_test_plancache VALUES(+4567890123456789,'4567890123456789');
INSERT 0 1
postgres=# INSERT INTO tab_test_plancache VALUES('+4567890123456789','-4567890123456789');
INSERT 0 1

3.建立並使用快取的計劃

postgres=# PREPARE prepstmt AS SELECT * FROM tab_test_plancache ORDER BY q1, q2;
PREPARE
postgres=# EXECUTE prepstmt;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)

並且包含一個帶有繫結變數的

postgres=# PREPARE prepstmt2(bigint) AS SELECT * FROM tab_test_plancache WHERE q1 = $1 ORDER BY q1, q2;
PREPARE
postgres=# EXECUTE prepstmt2(123);
 q1  |        q2
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

4.刪除臨時表，檢視現象

postgres=# DROP TABLE tab_test_plancache;
DROP TABLE
postgres=# EXECUTE prepstmt;
ERROR:  relation "tab_test_plancache" does not exist on dn_6001_6002
postgres=# EXECUTE prepstmt2(123);
ERROR:  relation "tab_test_plancache" does not exist on dn_6001_6002

重建臨時表

postgres=# select * from tab_test_plancache;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# EXECUTE prepstmt;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# EXECUTE prepstmt2(123);
 q1  |        q2
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

這表明原始計劃是純文字的，不依賴於OID

5.prepared statements應該防止在輸出的tupdesc中更改， 因為clients可能不希望這種情況瞬間改變

postgres=# ALTER TABLE tab_test_plancache ADD COLUMN q3 bigint;
ALTER TABLE
postgres=# EXECUTE prepstmt;
ERROR:  cached plan must not change result type
postgres=# EXECUTE prepstmt2(123);
ERROR:  cached plan must not change result type

例子裡增加了一列，但是報出了快取的計劃不能更改結果型別，可以透過還原原來表的結構解決

postgres=# ALTER TABLE tab_test_plancache ADD COLUMN q3 bigint;
ALTER TABLE
postgres=# select * from tab_test_plancache;
        q1        |        q2         | q3
------------------+-------------------+----
              123 |               456 |
              123 |  4567890123456789 |
 4567890123456789 | -4567890123456789 |
 4567890123456789 |               123 |
 4567890123456789 |  4567890123456789 |
(5 rows)
postgres=# ALTER TABLE tab_test_plancache DROP COLUMN q3;
ALTER TABLE
postgres=# EXECUTE prepstmt;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# EXECUTE prepstmt2(123);
 q1  |        q2
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

6.檢查使用檢視的有效性

如果嘗試使用一個檢視的話，這個檢視不會直接用於生成的計劃中，但也是有效的

postgres=# CREATE TEMP VIEW pcacheview AS
postgres-#   SELECT * FROM tab_test_plancache;
CREATE VIEW
postgres=# PREPARE vprep AS SELECT * FROM pcacheview ORDER BY q1, q2;
PREPARE
postgres=# EXECUTE vprep;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# CREATE OR REPLACE TEMP VIEW pcacheview AS
  SELECT q1, q2+1 AS q2 FROM tab_test_plancache ORDER BY q1, q2;
CREATE VIEW
postgres=# EXECUTE vprep;
        q1        |        q2
------------------+-------------------
              123 |               457
              123 |  4567890123456790
 4567890123456789 | -4567890123456788
 4567890123456789 |               124
 4567890123456789 |  4567890123456790
(5 rows)

7.檢查基本 SPI plan 是否有效

postgres=# create function cache_test(int) returns int as $$
postgres$# declare total int;
postgres$# begin
postgres$# create table t1_plancache(f1 int);
postgres$# insert into t1_plancache values($1);
postgres$# insert into t1_plancache values(11);
postgres$# insert into t1_plancache values(12);
postgres$# insert into t1_plancache values(13);
postgres$# select sum(f1) into total from t1_plancache;
postgres$# drop table t1_plancache;
postgres$# return total;
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select cache_test(1);
 cache_test
------------
         37
(1 row)
postgres=# select cache_test(2);
 cache_test
------------
         38
(1 row)
postgres=# select cache_test(3);
 cache_test
------------
         39
(1 row)

8.檢查plpgsql“簡單表示式”的有效性

postgres=# create temp view v1 as
postgres-#   select 2+2 as f1;
CREATE VIEW
postgres=# create function cache_test_2() returns int as $$
postgres$# begin
postgres$# return f1 from v1;
postgres$# end$$ language plpgsql;
CREATE FUNCTION
postgres=# select cache_test_2();
 cache_test_2
--------------
            4
(1 row)
postgres=# create or replace temp view v1 as
postgres-#   select 2+2+4 as f1;
CREATE VIEW
postgres=# select cache_test_2();
 cache_test_2
--------------
            8
(1 row)

9.檢查快取執行計劃使用與search_path影響

可以看到，兩個schema下都有同一張表，修改了search_path後，快取執行計劃執行的是search_path下的表，所以快取執行計劃會受search_path影響。

postgres=# create schema s1
postgres-#   create table abc (f1 int);
CREATE SCHEMA
postgres=# create schema s2
postgres-#   create table abc (f1 int);
CREATE SCHEMA
postgres=# insert into s1.abc values(123);
INSERT 0 1
postgres=# insert into s2.abc values(456);
INSERT 0 1
postgres=# set search_path = s1;
SET
postgres=# prepare p1 as select f1 from abc;
PREPARE
postgres=# execute p1;
 f1
-----
 123
(1 row)
postgres=# set search_path = s2;
SET
postgres=# select f1 from abc;
 f1
-----
 456
(1 row)
postgres=# execute p1;
 f1
-----
 456
(1 row)
postgres=# alter table s1.abc add column f2 float8;
ALTER TABLE
postgres=# execute p1;
 f1
-----
 456
(1 row)
postgres=# drop schema s1 cascade;
NOTICE:  drop cascades to table s1.abc
DROP SCHEMA
postgres=# drop schema s2 cascade;
NOTICE:  drop cascades to table abc
DROP SCHEMA
postgres=# reset search_path;
RESET

10.檢查regclass常量是否有效

postgres=# create  sequence seq;
CREATE SEQUENCE
postgres=# prepare p2 as select nextval('seq');
PREPARE
postgres=# execute p2;
##  nextval
```
   1
```
(1 row)
postgres=# drop sequence seq;
DROP SEQUENCE
postgres=# create  sequence seq;
CREATE SEQUENCE
postgres=# execute p2;
##  nextval
```
   1
```
(1 row)

11.檢查DDL，然後立即重新使用SPI plan

postgres=# create function cachebug() returns void as $$
postgres$# declare r int;
postgres$# begin
postgres$#   drop table if exists temptable cascade;
postgres$#   create temp table temptable as select * from generate_series(1,3) as f1;
postgres$#   create temp view vv as select * from temptable;
postgres$#   for r in select * from vv order by 1 loop
postgres$#     raise notice '%', r;
postgres$#   end loop;
postgres$# end$$ language plpgsql;
CREATE FUNCTION
postgres=# select cachebug();
NOTICE:  table "temptable" does not exist, skipping
CONTEXT:  SQL statement "drop table if exists temptable cascade"
PL/pgSQL function cachebug() line 4 at SQL statement
referenced column: cachebug
NOTICE:  1
CONTEXT:  referenced column: cachebug
NOTICE:  2
CONTEXT:  referenced column: cachebug
NOTICE:  3
CONTEXT:  referenced column: cachebug
 cachebug
----------
(1 row)
postgres=# select cachebug();
NOTICE:  drop cascades to view vv
CONTEXT:  SQL statement "drop table if exists temptable cascade"
PL/pgSQL function cachebug() line 4 at SQL statement
referenced column: cachebug
NOTICE:  1
CONTEXT:  referenced column: cachebug
NOTICE:  2
CONTEXT:  referenced column: cachebug
NOTICE:  3
CONTEXT:  referenced column: cachebug
 cachebug
----------
(1 row)