BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails - Mailing list pgsql-bugs
From | PG Bug reporting form |
---|---|
Subject | BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails |
Date | |
Msg-id | 17650-4523221bc4eb0c33@postgresql.org Whole thread Raw |
Responses |
Re: BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails
Re: BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails |
List | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17650 Logged by: dafoer Email address: dafoer_x@163.com PostgreSQL version: 14.0 Operating system: centos7.6 Description: The clipping function of partition table cannot be carried out normally in the planning stage. The extension protocol cannot be clipped in the sixth execution. When concurrency is high, lock contention is serious postgres=# prepare fun3(int, int) as select * from t_range where f1=$1 and f2 = $2 + 2; PREPARE Time: 0.439 ms postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 1.431 ms postgres=# postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 0.451 ms postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 0.430 ms postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 0.421 ms postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 0.416 ms postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 7.568 ms (-- It took too much time <<<<<<<<=======================) postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) Time: 0.450 ms postgres=# execute fun3(3486,201703); f1 | f2 | f3 | f4 ------+--------+------+---------------------------------- 3486 | 201705 | 3486 | ab4f2b5fd96ca65349119909c1eada2d (1 row) use cases: prepare fun3(int, int) as select * from t_range where f1=$1 and f2 = $2 + 2; execute fun3(3486,201703); -- create table create or replace function init_t_range_data(start_date int) returns void as $$ DECLARE i int; sql text; pd int; BEGIN pd := start_date; for i in 1..11 loop sql:=format('create table if not exists t_range_%s PARTITION OF t_range for values from (%s) to (%s);', pd, pd, pd+1); execute sql; raise notice '%',sql; sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000), md5(t::varchar) from generate_series(1,100000) t;', pd); execute sql; raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f3_idx on t_range_%s(f3);',pd,pd); execute sql; raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f1_idx on t_range_%s(f1);',pd,pd); execute sql; raise notice '%',sql; pd:=pd+1; end loop; pd := start_date; sql:=format('create table if not exists t_range_%s PARTITION OF t_range for values from (%s) to (%s);', pd+11, pd+11, pd+100); execute sql; raise notice '%',sql; sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000), md5(t::varchar) from generate_series(1,100000) t;', pd+11); execute sql; raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f3_idx on t_range_%s(f3);',pd+11,pd+11); execute sql; raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f1_idx on t_range_%s(f1);',pd+11,pd+11); execute sql; raise notice '%',sql; end; $$ language plpgsql; drop table if exists t_range cascade; create table t_range (f1 bigint,f2 int , f3 integer, f4 varchar(100) ) partition by range ( f2 ); select init_t_range_data(201701); create or replace function init_t_range_data(start_date int) returns void as $$ DECLARE i int; sql text; pd int; BEGIN pd := start_date; for i in 1..11 loop sql:=format('create table if not exists t_range_%s PARTITION OF t_range for values from (%s) to (%s);', pd, pd, pd+1); execute sql; raise notice '%',sql; --sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000), md5(t::varchar) from generate_series(1,100000) t;', pd); --execute sql; --raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f3_idx on t_range_%s(f3);',pd,pd); execute sql; raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f1_idx on t_range_%s(f1);',pd,pd); execute sql; raise notice '%',sql; pd:=pd+1; end loop; pd := start_date; sql:=format('create table if not exists t_range_%s PARTITION OF t_range for values from (%s) to (%s);', pd+11, pd+11, pd+100); execute sql; raise notice '%',sql; --sql:=format('insert into t_range(f1,f2,f3,f4) select t, %s, mod(t,10000), md5(t::varchar) from generate_series(1,100000) t;', pd+11); --execute sql; --raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f3_idx on t_range_%s(f3);',pd+11,pd+11); execute sql; raise notice '%',sql; sql:=format('create index if not exists t_range_%s_f1_idx on t_range_%s(f1);',pd+11,pd+11); execute sql; raise notice '%',sql; end; $$ language plpgsql; select init_t_range_data(201601); select init_t_range_data(201801); select init_t_range_data(201901); select init_t_range_data(202001); select init_t_range_data(202101); select init_t_range_data(202201); select init_t_range_data(202301); select init_t_range_data(202401); select init_t_range_data(202501); vacuum (analyze, verbose) t_range;
pgsql-bugs by date: