diff options
Diffstat (limited to 'src/test/regress/expected/plancache.out')
| -rw-r--r-- | src/test/regress/expected/plancache.out | 400 |
1 files changed, 400 insertions, 0 deletions
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out new file mode 100644 index 0000000..4e59188 --- /dev/null +++ b/src/test/regress/expected/plancache.out @@ -0,0 +1,400 @@ +-- +-- Tests to exercise the plan caching/invalidation mechanism +-- +CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl; +-- create and use a cached plan +PREPARE prepstmt AS SELECT * FROM pcachetest; +EXECUTE prepstmt; + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +-- and one with parameters +PREPARE prepstmt2(bigint) AS SELECT * FROM pcachetest WHERE q1 = $1; +EXECUTE prepstmt2(123); + q1 | q2 +-----+------------------ + 123 | 456 + 123 | 4567890123456789 +(2 rows) + +-- invalidate the plans and see what happens +DROP TABLE pcachetest; +EXECUTE prepstmt; +ERROR: relation "pcachetest" does not exist +EXECUTE prepstmt2(123); +ERROR: relation "pcachetest" does not exist +-- recreate the temp table (this demonstrates that the raw plan is +-- purely textual and doesn't depend on OIDs, for instance) +CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl ORDER BY 2; +EXECUTE prepstmt; + q1 | q2 +------------------+------------------- + 4567890123456789 | -4567890123456789 + 4567890123456789 | 123 + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 4567890123456789 +(5 rows) + +EXECUTE prepstmt2(123); + q1 | q2 +-----+------------------ + 123 | 456 + 123 | 4567890123456789 +(2 rows) + +-- prepared statements should prevent change in output tupdesc, +-- since clients probably aren't expecting that to change on the fly +ALTER TABLE pcachetest ADD COLUMN q3 bigint; +EXECUTE prepstmt; +ERROR: cached plan must not change result type +EXECUTE prepstmt2(123); +ERROR: cached plan must not change result type +-- but we're nice guys and will let you undo your mistake +ALTER TABLE pcachetest DROP COLUMN q3; +EXECUTE prepstmt; + q1 | q2 +------------------+------------------- + 4567890123456789 | -4567890123456789 + 4567890123456789 | 123 + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 4567890123456789 +(5 rows) + +EXECUTE prepstmt2(123); + q1 | q2 +-----+------------------ + 123 | 456 + 123 | 4567890123456789 +(2 rows) + +-- Try it with a view, which isn't directly used in the resulting plan +-- but should trigger invalidation anyway +CREATE TEMP VIEW pcacheview AS + SELECT * FROM pcachetest; +PREPARE vprep AS SELECT * FROM pcacheview; +EXECUTE vprep; + q1 | q2 +------------------+------------------- + 4567890123456789 | -4567890123456789 + 4567890123456789 | 123 + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 4567890123456789 +(5 rows) + +CREATE OR REPLACE TEMP VIEW pcacheview AS + SELECT q1, q2/2 AS q2 FROM pcachetest; +EXECUTE vprep; + q1 | q2 +------------------+------------------- + 4567890123456789 | -2283945061728394 + 4567890123456789 | 61 + 123 | 228 + 123 | 2283945061728394 + 4567890123456789 | 2283945061728394 +(5 rows) + +-- Check basic SPI plan invalidation +create function cache_test(int) returns int as $$ +declare total int; +begin + create temp table t1(f1 int); + insert into t1 values($1); + insert into t1 values(11); + insert into t1 values(12); + insert into t1 values(13); + select sum(f1) into total from t1; + drop table t1; + return total; +end +$$ language plpgsql; +select cache_test(1); + cache_test +------------ + 37 +(1 row) + +select cache_test(2); + cache_test +------------ + 38 +(1 row) + +select cache_test(3); + cache_test +------------ + 39 +(1 row) + +-- Check invalidation of plpgsql "simple expression" +create temp view v1 as + select 2+2 as f1; +create function cache_test_2() returns int as $$ +begin + return f1 from v1; +end$$ language plpgsql; +select cache_test_2(); + cache_test_2 +-------------- + 4 +(1 row) + +create or replace temp view v1 as + select 2+2+4 as f1; +select cache_test_2(); + cache_test_2 +-------------- + 8 +(1 row) + +create or replace temp view v1 as + select 2+2+4+(select max(unique1) from tenk1) as f1; +select cache_test_2(); + cache_test_2 +-------------- + 10007 +(1 row) + +--- Check that change of search_path is honored when re-using cached plan +create schema s1 + create table abc (f1 int); +create schema s2 + create table abc (f1 int); +insert into s1.abc values(123); +insert into s2.abc values(456); +set search_path = s1; +prepare p1 as select f1 from abc; +execute p1; + f1 +----- + 123 +(1 row) + +set search_path = s2; +select f1 from abc; + f1 +----- + 456 +(1 row) + +execute p1; + f1 +----- + 456 +(1 row) + +alter table s1.abc add column f2 float8; -- force replan +execute p1; + f1 +----- + 456 +(1 row) + +drop schema s1 cascade; +NOTICE: drop cascades to table s1.abc +drop schema s2 cascade; +NOTICE: drop cascades to table abc +reset search_path; +-- Check that invalidation deals with regclass constants +create temp sequence seq; +prepare p2 as select nextval('seq'); +execute p2; + nextval +--------- + 1 +(1 row) + +drop sequence seq; +create temp sequence seq; +execute p2; + nextval +--------- + 1 +(1 row) + +-- Check DDL via SPI, immediately followed by SPI plan re-use +-- (bug in original coding) +create function cachebug() returns void as $$ +declare r int; +begin + drop table if exists temptable cascade; + create temp table temptable as select * from generate_series(1,3) as f1; + create temp view vv as select * from temptable; + for r in select * from vv loop + raise notice '%', r; + end loop; +end$$ language plpgsql; +select cachebug(); +NOTICE: table "temptable" does not exist, skipping +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 + cachebug +---------- + +(1 row) + +select cachebug(); +NOTICE: drop cascades to view vv +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 + cachebug +---------- + +(1 row) + +-- Check that addition or removal of any partition is correctly dealt with by +-- default partition table when it is being used in prepared statement. +create table pc_list_parted (a int) partition by list(a); +create table pc_list_part_null partition of pc_list_parted for values in (null); +create table pc_list_part_1 partition of pc_list_parted for values in (1); +create table pc_list_part_def partition of pc_list_parted default; +prepare pstmt_def_insert (int) as insert into pc_list_part_def values($1); +-- should fail +execute pstmt_def_insert(null); +ERROR: new row for relation "pc_list_part_def" violates partition constraint +DETAIL: Failing row contains (null). +execute pstmt_def_insert(1); +ERROR: new row for relation "pc_list_part_def" violates partition constraint +DETAIL: Failing row contains (1). +create table pc_list_part_2 partition of pc_list_parted for values in (2); +execute pstmt_def_insert(2); +ERROR: new row for relation "pc_list_part_def" violates partition constraint +DETAIL: Failing row contains (2). +alter table pc_list_parted detach partition pc_list_part_null; +-- should be ok +execute pstmt_def_insert(null); +drop table pc_list_part_1; +-- should be ok +execute pstmt_def_insert(1); +drop table pc_list_parted, pc_list_part_null; +deallocate pstmt_def_insert; +-- Test plan_cache_mode +create table test_mode (a int); +insert into test_mode select 1 from generate_series(1,1000) union all select 2; +create index on test_mode (a); +analyze test_mode; +prepare test_mode_pp (int) as select count(*) from test_mode where a = $1; +select name, generic_plans, custom_plans from pg_prepared_statements + where name = 'test_mode_pp'; + name | generic_plans | custom_plans +--------------+---------------+-------------- + test_mode_pp | 0 | 0 +(1 row) + +-- up to 5 executions, custom plan is used +set plan_cache_mode to auto; +explain (costs off) execute test_mode_pp(2); + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Index Only Scan using test_mode_a_idx on test_mode + Index Cond: (a = 2) +(3 rows) + +select name, generic_plans, custom_plans from pg_prepared_statements + where name = 'test_mode_pp'; + name | generic_plans | custom_plans +--------------+---------------+-------------- + test_mode_pp | 0 | 1 +(1 row) + +-- force generic plan +set plan_cache_mode to force_generic_plan; +explain (costs off) execute test_mode_pp(2); + QUERY PLAN +----------------------------- + Aggregate + -> Seq Scan on test_mode + Filter: (a = $1) +(3 rows) + +select name, generic_plans, custom_plans from pg_prepared_statements + where name = 'test_mode_pp'; + name | generic_plans | custom_plans +--------------+---------------+-------------- + test_mode_pp | 1 | 1 +(1 row) + +-- get to generic plan by 5 executions +set plan_cache_mode to auto; +execute test_mode_pp(1); -- 1x + count +------- + 1000 +(1 row) + +execute test_mode_pp(1); -- 2x + count +------- + 1000 +(1 row) + +execute test_mode_pp(1); -- 3x + count +------- + 1000 +(1 row) + +execute test_mode_pp(1); -- 4x + count +------- + 1000 +(1 row) + +select name, generic_plans, custom_plans from pg_prepared_statements + where name = 'test_mode_pp'; + name | generic_plans | custom_plans +--------------+---------------+-------------- + test_mode_pp | 1 | 5 +(1 row) + +execute test_mode_pp(1); -- 5x + count +------- + 1000 +(1 row) + +select name, generic_plans, custom_plans from pg_prepared_statements + where name = 'test_mode_pp'; + name | generic_plans | custom_plans +--------------+---------------+-------------- + test_mode_pp | 2 | 5 +(1 row) + +-- we should now get a really bad plan +explain (costs off) execute test_mode_pp(2); + QUERY PLAN +----------------------------- + Aggregate + -> Seq Scan on test_mode + Filter: (a = $1) +(3 rows) + +-- but we can force a custom plan +set plan_cache_mode to force_custom_plan; +explain (costs off) execute test_mode_pp(2); + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Index Only Scan using test_mode_a_idx on test_mode + Index Cond: (a = 2) +(3 rows) + +select name, generic_plans, custom_plans from pg_prepared_statements + where name = 'test_mode_pp'; + name | generic_plans | custom_plans +--------------+---------------+-------------- + test_mode_pp | 3 | 6 +(1 row) + +drop table test_mode; |