From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dafoer_x@163.com
Subject: BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails
Date: Tue, 18 Oct 2022 13:11:13 +0000
Message-ID: <17650-4523221bc4eb0c33@postgresql.org> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, dafoer_x@163.com
Subject: Re: BUG #17650: For the sixth time, the clipping function in the 120 partition table planning stage fails
In-Reply-To: <17650-4523221bc4eb0c33@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox