Обсуждение: BUG #15320: = any (array(SQL)) ERROR: invalid memory alloc requestsize 1073741824

The following bug has been logged on the website:

Bug reference:      15320
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 11beta2
Operating system:   CentOS 7.x x64
Description:

the error is:

```
create table tbl (uid int8 primary key, pid int8);
insert into tbl select generate_series(1,100000000), random()*100000;

postgres=# explain select count(*) from tbl where uid = any (array(select
uid from tbl limit 100000000))    ;
                                     QUERY PLAN
        
-------------------------------------------------------------------------------------
 Aggregate  (cost=1635653.23..1635653.24 rows=1 width=8)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..1635635.23 rows=100000000 width=8)
           ->  Seq Scan on tbl tbl_1  (cost=0.00..2289638.88 rows=139984688
width=8)
   ->  Index Only Scan using pk on tbl  (cost=0.57..17.98 rows=10 width=0)
         Index Cond: (uid = ANY ($0))
(6 rows)

postgres=# explain analyze select count(*) from tbl where uid = any
(array(select uid from tbl limit 100000000))    ;
ERROR:  XX000: invalid memory alloc request size 1073741824
LOCATION:  repalloc, mcxt.c:1050
Time: 24133.852 ms (00:24.134)
```

is it a bug?
thanks, 
best regards. 
digoal

Hi,

On 2018-08-10 02:00:56 +0000, PG Bug reporting form wrote:
> postgres=# explain analyze select count(*) from tbl where uid = any
> (array(select uid from tbl limit 100000000))    ;
> ERROR:  XX000: invalid memory alloc request size 1073741824
> LOCATION:  repalloc, mcxt.c:1050
> Time: 24133.852 ms (00:24.134)
> ```
> 
> is it a bug?

No, not in my opinion. You're building a very large array. Arrays are
stored in memory. Allocation sizes in postgres are limited in many
places.

You could argue for removing the limit in this case, but that'd not make
it a bug. And I doubt it's worth changing this. Your array is going to
be huge either way, and we limit arrays (and other datums) to 1GB. We
could fix the growth of the array here to fail a bit later, but that's
it.

Greetings,

Andres Freund