BUG #14191: plpgsql run infinit when i use bulk query first time

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)

Browse pgsql-bugs by date

  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