Thread: 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@comcast.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Scott Bailey <artacus@comcast.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@comcast.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@comcast.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@comcast.net>: > Pavel Stehule wrote: >> >> Hello >> >> 2009/11/19 Scott Bailey <artacus@comcast.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 >