Possible bug with array_agg

On 8.4.0 I found that array_agg does not return a value when fed more
than 12,000 values. (12,000 worked and 13,000 did not.)

Probably not a big deal because its not something you would typically
do. I was testing something else and ran into it. But if there is a
limit there, we should probably document it.

Scott Bailey

Hello

2009/11/19 Scott Bailey <artacus(at)comcast(dot)net>:
> On 8.4.0 I found that array_agg does not return a value when fed more than
> 12,000 values. (12,000 worked and 13,000 did not.)
>

can you send a query?

postgres=# create table f(a int);
CREATE TABLE
postgres=# insert into f select * from generate_series(1,100000);
INSERT 0 100000
postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
count
--------
100000
(1 row)

it's look well

Regards
Pavel Stehule

> Probably not a big deal because its not something you would typically do. I
> was testing something else and ran into it. But if there is a limit there,
> we should probably document it.
>
> Scott Bailey
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Scott Bailey <artacus(at)comcast(dot)net> writes:
> On 8.4.0 I found that array_agg does not return a value when fed more
> than 12,000 values. (12,000 worked and 13,000 did not.)

What do you mean by "does not return a value"?

In general, giving a specific test case is a lot more helpful than
this sort of vague report. Whatever you're seeing could easily
depend on the specific data you're aggregating, for example.

regards, tom lane

Pavel Stehule wrote:
> Hello
>
> 2009/11/19 Scott Bailey <artacus(at)comcast(dot)net>:
>> On 8.4.0 I found that array_agg does not return a value when fed more than
>> 12,000 values. (12,000 worked and 13,000 did not.)
>>
>
> can you send a query?
>
> postgres=# create table f(a int);
> CREATE TABLE
> postgres=# insert into f select * from generate_series(1,100000);
> INSERT 0 100000
> postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
> count
> --------
> 100000
> (1 row)
>
> it's look well

As you were. Above worked for me as well. It appears to be a problem
with pgAdmin.

-- Returns nothing
SELECT a
FROM (
select array_agg(a) a from f
) s

-- Works
SELECT a
FROM (
select array_agg(a) a from f
WHERE a < 12000
) s

-- Returns nothing
SELECT a
FROM (
select array_agg(a) a from f
WHERE a < 13000
) s

When I ran in different client all queries returned results.

Scott

Le jeudi 19 novembre 2009 à 20:45:36, Scott Bailey a écrit :
> Pavel Stehule wrote:
> > Hello
> >
> > 2009/11/19 Scott Bailey <artacus(at)comcast(dot)net>:
> >> On 8.4.0 I found that array_agg does not return a value when fed more
> >> than 12,000 values. (12,000 worked and 13,000 did not.)
> >
> > can you send a query?
> >
> > postgres=# create table f(a int);
> > CREATE TABLE
> > postgres=# insert into f select * from generate_series(1,100000);
> > INSERT 0 100000
> > postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
> > count
> > --------
> > 100000
> > (1 row)
> >
> > it's look well
>
> As you were. Above worked for me as well. It appears to be a problem
> with pgAdmin.
>
> -- Returns nothing
> SELECT a
> FROM (
> select array_agg(a) a from f
> ) s
>
> -- Works
> SELECT a
> FROM (
> select array_agg(a) a from f
> WHERE a < 12000
> ) s
>
> -- Returns nothing
> SELECT a
> FROM (
> select array_agg(a) a from f
> WHERE a < 13000
> ) s
>

I'm not sure which release you use, but it works for me (1.10 and 1.11). The
result of the first query is badly displayed (remember that it tries to
display an array of 100000 integers), but the query returns something that
pgAdmin tries to display. The others work too but the display is much better.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

>
> I'm not sure which release you use, but it works for me (1.10 and 1.11). The
> result of the first query is badly displayed (remember that it tries to
> display an array of 100000 integers), but the query returns something that
> pgAdmin tries to display. The others work too but the display is much better.
>
>

Using 1.10. It displays nothing on Windows version. Displays a bunch of
overlapping text on Linux. Oh well, that doesn't matter, as long as
array_agg is doing what it should.

Scott

2009/11/19 Scott Bailey <artacus(at)comcast(dot)net>:
> Pavel Stehule wrote:
>>
>> Hello
>>
>> 2009/11/19 Scott Bailey <artacus(at)comcast(dot)net>:
>>>
>>> On 8.4.0 I found that array_agg does not return a value when fed more
>>> than
>>> 12,000 values. (12,000 worked and 13,000 did not.)
>>>
>>
>> can you send a query?
>>
>> postgres=# create table f(a int);
>> CREATE TABLE
>> postgres=# insert into f select * from generate_series(1,100000);
>> INSERT 0 100000
>> postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
>>  count
>> --------
>>  100000
>> (1 row)
>>
>> it's look well
>
> As you were. Above worked for me as well. It appears to be a problem with
> pgAdmin.
>
> -- Returns nothing
> SELECT a
> FROM (
>  select array_agg(a) a  from f
> ) s
>
> -- Works
> SELECT a
> FROM (
>  select array_agg(a) a  from f
>  WHERE a < 12000
> ) s
>
> -- Returns nothing
> SELECT a
> FROM (
>  select array_agg(a) a  from f
>  WHERE a < 13000
> ) s
>
> When I ran in different client all queries returned results.

This is pgAdmin problem - to long for cell - I thing. Usually I am
testing problematic queries in psql console.

try SELECT ARRAY(SELECT * FROM f)

it would not work in pgAdmin too - probably.

Regards
Pavel Stehule

>
> Scott
>