-- PARALLEL
--
-create or replace function parallel_restricted(int) returns int as
+create function sp_parallel_restricted(int) returns int as
$$begin return $1; end$$ language plpgsql parallel restricted;
-- Serializable isolation would disable parallel query, so explicitly use an
select round(avg(aa)), sum(aa) from a_star;
select round(avg(aa)), sum(aa) from a_star a3;
+-- Temporary hack to investigate whether extra vacuum/analyze is happening
+select relname, relpages, reltuples
+from pg_class
+where relname like '__star' order by relname;
+
-- Disable Parallel Append
alter table a_star reset (parallel_workers);
alter table b_star reset (parallel_workers);
reset enable_parallel_append;
-- Parallel Append that runs serially
-create or replace function foobar() returns setof text as
+create function sp_test_func() returns setof text as
$$ select 'foo'::varchar union all select 'bar'::varchar $$
language sql stable;
-select foobar() order by 1;
+select sp_test_func() order by 1;
+
+-- Parallel Append is not to be used when the subpath depends on the outer param
+create table part_pa_test(a int, b int) partition by range(a);
+create table part_pa_test_p1 partition of part_pa_test for values from (minvalue) to (0);
+create table part_pa_test_p2 partition of part_pa_test for values from (0) to (maxvalue);
+explain (costs off)
+ select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
+ from part_pa_test pa2;
+drop table part_pa_test;
-- test with leader participation disabled
set parallel_leader_participation = off;
-- test that parallel_restricted function doesn't run in worker
alter table tenk1 set (parallel_workers = 4);
explain (verbose, costs off)
-select parallel_restricted(unique1) from tenk1
+select sp_parallel_restricted(unique1) from tenk1
where stringu1 = 'GRAAAA' order by 1;
-- test parallel plan when group by expression is in target list.
-- test that parallel plan for aggregates is not selected when
-- target list contains parallel restricted clause.
explain (costs off)
- select sum(parallel_restricted(unique1)) from tenk1
- group by(parallel_restricted(unique1));
+ select sum(sp_parallel_restricted(unique1)) from tenk1
+ group by(sp_parallel_restricted(unique1));
-- test prepared statement
prepare tenk1_count(integer) As select count((unique1)) from tenk1 where hundred > $1;
select count(*) from tenk1 group by twenty;
--test expressions in targetlist are pushed down for gather merge
-create or replace function simple_func(var1 integer) returns integer
+create function sp_simple_func(var1 integer) returns integer
as $$
begin
return var1 + 10;
$$ language plpgsql PARALLEL SAFE;
explain (costs off, verbose)
- select ten, simple_func(ten) from tenk1 where ten < 100 order by ten;
+ select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten;
+
+drop function sp_simple_func(integer);
+
+-- test handling of SRFs in targetlist (bug in 10.0)
+
+explain (costs off)
+ select count(*), generate_series(1,2) from tenk1 group by twenty;
-drop function simple_func(integer);
+select count(*), generate_series(1,2) from tenk1 group by twenty;
-- test gather merge with parallel leader participation disabled
set parallel_leader_participation = off;
ROLLBACK TO SAVEPOINT settings;
-- exercise record typmod remapping between backends
-CREATE OR REPLACE FUNCTION make_record(n int)
+CREATE FUNCTION make_record(n int)
RETURNS RECORD LANGUAGE plpgsql PARALLEL SAFE AS
$$
BEGIN
reset force_parallel_mode;
reset role;
+-- Window function calculation can't be pushed to workers.
+explain (costs off, verbose)
+ select count(*) from tenk1 a where (unique1, two) in
+ (select unique1, row_number() over() from tenk1 b);
+
+
+-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
+explain (costs off)
+ select * from tenk1 a where two in
+ (select two from tenk1 b where stringu1 like '%AAAA' limit 3);
+
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
SELECT * FROM information_schema.foreign_data_wrapper_options
ORDER BY 1, 2, 3;
+-- test passing expanded-value representations to workers
+CREATE FUNCTION make_some_array(int,int) returns int[] as
+$$declare x int[];
+ begin
+ x[1] := $1;
+ x[2] := $2;
+ return x;
+ end$$ language plpgsql parallel safe;
+CREATE TABLE fooarr(f1 text, f2 int[], f3 text);
+INSERT INTO fooarr VALUES('1', ARRAY[1,2], 'one');
+
+PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2;
+EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2));
+EXECUTE pstmt('1', make_some_array(1,2));
+DEALLOCATE pstmt;
+
+-- test interaction between subquery and partial_paths
+CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS SELECT * FROM tenk1;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM tenk1_vw_sec
+ WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100;
+
rollback;
projects / postgresql / blobdiff