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)
「喜歡這篇文章,您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容,轉載時必須標注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容,歡迎發送郵件至:contact@modb.pro進行舉報,并提供相關證據,一經查實,墨天輪將立刻刪除相關內容。




