From: | Nikolay(dot)Nikitin(at)infowatch(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14100: Large memory consumption in a partitioning insert of great values |
Date: | 2016-04-19 08:28:47 |
Message-ID: | 20160419082847.22924.13764@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: 14100
Logged by: Nikolay
Email address: Nikolay(dot)Nikitin(at)infowatch(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitriy Sarafannikov | 2016-04-19 09:43:24 | Re: Too many files in pg_replslot folder |
Previous Message | zhaozp@uxsino.com | 2016-04-19 03:02:49 | Re: BUG #14096: run pgbench, db crash |