BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails
От | PG Bug reporting form |
---|---|
Тема | BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails |
Дата | |
Msg-id | 17650-4523221bc4eb0c33@postgresql.org обсуждение исходный текст |
Ответы |
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 |
Список | 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 по дате отправления:
Предыдущее
От: Julien RouhaudДата:
Сообщение: Re: Aw: BUG #17647: 12.12 package has difference on ubuntu 18.04
Следующее
От: Julien RouhaudДата:
Сообщение: Re: BUG #17647: 12.12 package has difference on ubuntu 18.04