From: | digoal(at)126(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14191: plpgsql run infinit when i use bulk query first time |
Date: | 2016-06-15 05:47:52 |
Message-ID: | 20160615054752.5792.1646@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14191
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.5.3
Operating system: CentOS 7.x x64
Description:
create table test(id int, info text);
CREATE OR REPLACE FUNCTION public.f_bulk_insert1(i_k integer[], i_v
text[])
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
declare
i_length int := array_length(i_k,1);
s timestamp;
e timestamp;
begin
s := clock_timestamp();
raise notice 'start: %', s;
insert into test select i_k[i], i_v[i] from generate_series(1, i_length)
t(i);
e := clock_timestamp();
raise notice 'end: %, %', e, e-s;
end;
$function$;
the first call for many tuples will run forever:
select f_bulk_insert1(k,v) from (
select array_agg(k) as k, array_agg(v) as v from (
select k, md5(random()::text) v from generate_series(1,1000000) t(k)
) t
) t;
NOTICE: start: 2016-06-15 13:47:09.838093
..... and then run forever ....
perf top is:
Overhead Shared Object Symbol
16.90% [unknown] [.] 0x00000000006aacb4
5.58% [unknown] [.] 0x00000000006aacb8
5.51% [unknown] [.] 0x00000000006aacb0
5.32% [unknown] [.] 0x00000000006aac40
3.72% [unknown] [.] 0x00000000006aac2b
3.68% [unknown] [.] 0x00000000006aad2a
3.63% [unknown] [.] 0x00000000006aacc4
3.63% [unknown] [.] 0x00000000006aac27
2.90% [kernel] [k] clear_page_c
1.76% [unknown] [.] 0x00000000006aacc1
1.62% [unknown] [.] 0x00000000006aac08
1.54% [unknown] [.] 0x00000000006aac1d
1.50% [unknown] [.] 0x00000000006aad24
0.64% [kernel] [k] copy_user_generic_string
0.43% [unknown] [.] 0x00007f2fd77159e1
0.41% [unknown] [.] 0x00007f2fd77156e8
0.40% [unknown] [.] 0x000000000079e9e7
but when i run few tuples 5 times first, then run many more tuples , it's
ok.
select f_bulk_insert1(k,v) from (
select array_agg(k) as k, array_agg(v) as v from (
select k, md5(random()::text) v from generate_series(1,100) t(k)
) t
) t;
-- call 5 times.
and then call
select f_bulk_insert1(k,v) from (
select array_agg(k) as k, array_agg(v) as v from (
select k, md5(random()::text) v from generate_series(1,10000000) t(k)
) t
) t;
it's ok now.
postgres=# select f_bulk_insert1(k,v) from (
select array_agg(k) as k, array_agg(v) as v from (
select k, md5(random()::text) v from generate_series(1,10000000) t(k)
) t
) t;
NOTICE: start: 2016-06-15 13:46:18.212562
NOTICE: end: 2016-06-15 13:46:27.7474, 00:00:09.534838
f_bulk_insert1
----------------
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2016-06-15 07:37:12 | Re: BUG #13907: Restore materialized view throw permission denied |
Previous Message | cheddici@libero.it | 2016-06-15 04:38:55 |