From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: james.inform@pharmapp.de
Subject: BUG #17145: Invalid memory alloc request size, when searching in text column with big content > 250MB
Date: Sun, 15 Aug 2021 17:25:07 +0000
Message-ID: <17145-b7bf85f59bf37b13@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17145
Logged by: James Inform
Email address: james.inform@pharmapp.de
PostgreSQL version: 13.4
Operating system: Ubuntu 18.04 LTS / MacOS 10.15.7
Description:
Hi,
while importing some log data into a PostgreSQL table a came across this
issue.
When I try to search a text field in a where clause that holds more than 250
MB of text, PostgreSQL runs out of memory for the request.
You can reproduce this behaviour with the following sql statement:
with
q1 as
(
-- 260*1024*1024 = 260MB
select repeat('x',260*1024*1024) as mydata
)
select count(*) from q1 where lower(mydata) like '%a%'
;
ERROR: invalid memory alloc request size 1090519044
Using just a 250MB string:
with
q1 as
(
-- 250*1024*1024 = 250MB
select repeat('x',250*1024*1024) as mydata
)
select count(*) from q1 where lower(mydata) like '%a%'
;
Everything is fine!
The alloc request size seems to be 4 times the length of the text field.
The issue is also reproducible when using a table with a text column and
filling it with a string like above.
create table mytest as
with
q1 as
(
-- 260*1024*1024 = 260MB
select repeat('x',260*1024*1024) as mydata
)
select * from q1;
select count(*) from mytest where lower(mydata) like '%a%'
;
ERROR: invalid memory alloc request size 1090519044
Also creating an index e.g.
> create extension pg_trgm;
> create index on mytest using gin(lower(mydata) gin_trgm_ops);
is giving the error.
Is this an expected behaviour?
Why ist PostgreSQL allocating 4 times the column's memory when doing a
search like above?
It seems, that currently nobody will be able to use a text field with more
than 256 MB content with a function bases index nor search for a record
using the field in a where clause.
If the behaviour of using 4 times the memory is due to performance
considerations, then maybe the underlaying algorithm must be devided into an
efficient algo for < 256 MB and a less efficient but working without error
one for >= 256MB.
If we cannot change the behaviour, we should state this information in the
documentation, telling that you can store up to 1GB of string data into a
text column, but only up to 256 MB if you want to use the column for
complexer where clauses.
Cheers,
James
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, james.inform@pharmapp.de
Subject: Re: BUG #17145: Invalid memory alloc request size, when searching in text column with big content > 250MB
In-Reply-To: <17145-b7bf85f59bf37b13@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