BUG #14100: Large memory consumption in a partitioning insert of great values

Поиск
Список
Период
Сортировка
От Nikolay.Nikitin@infowatch.com
Тема BUG #14100: Large memory consumption in a partitioning insert of great values
Дата
Msg-id 20160419082847.22924.13764@wrigleys.postgresql.org
обсуждение исходный текст
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      14100
Logged by:          Nikolay
Email address:      Nikolay.Nikitin@infowatch.com
PostgreSQL version: 9.5.2
Operating system:   Red Hat server 6.7
Description:

If we generate big value with size X then server process takes 370M (empty
session process memory size)  + X.

select octet_length(string_agg(gen_random_bytes(1024), null::bytea)::bytea)
from generate_series(1,500 * 1024);


If we insert big value with size X in the usual table then server process
takes 370M + 3 * X.


create table test
(
  tablespace_id numeric,
  b bytea
);

create table test_1() inherits (test);

insert into test_1(tablespace_id, b)
select 1, string_agg(gen_random_bytes(1024), null::bytea)::bytea b
from generate_series(1,500 * 1024);


If we insert big value with size X in the partitioned table then server
process takes 370M + 6 * X.


create or replace function trg_fnc_test() returns trigger as $$
begin
 execute 'insert into test_' || new.tablespace_id || '(tablespace_id, b)
values($1, $2)'
 using new.tablespace_id, new.b;
 return null;
end;$$ language plpgsql;

create trigger trg_test_before_insert before insert on test for each row
execute procedure trg_fnc_test();

insert into test(tablespace_id, b)
select 1, string_agg(gen_random_bytes(1024), null::bytea)::bytea b
from generate_series(1,500 * 1024);


I see two bugs:

1. Server proccesses take memory from server memory without any limits.
10 concurrent processes which insert 1G values take 63G memory if it exists.

Or it will generate error if a memory is end.

2. Size 370M + 6 * X is very big. Insert of 1G value will take over 6G.
Can you reduce memory consumption to 2X or smaller in these cases?

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "zhaozp@uxsino.com"
Дата:
Сообщение: Re: BUG #14096: run pgbench, db crash
Следующее
От: Prashant Hunnure
Дата:
Сообщение: Fwd: Postgresql 9.4 installation error