[postgis-users] ERROR: array size exceeds the maximum allowed(134217727)

[Mike Leahy]

FWIW,  if I remove the entire where clause at the end of the statement, it 
works again (with st_union()) and is virtually instantaneous.

On Saturday 20 March 2010 18:10:38 Mike Leahy wrote:
> Hey,
> 
> The query (when it works) is virtually instantaneous when I use
>  st_memunion() (or when I took out part of the where clause - although now
>  I can't even get that to work, so maybe it was just lucky).  Each of the
>  subqueries also work fine without any obvious problem.
> 
> To me, it seems to be a combination of things that somehow add up to this
> limitation...I don't see what settings could affect the array size limit
>  (in postgresql.conf).  Can anyone point to a config option that might make
>  a difference, or maybe point to another place with settings I can tinker
>  with?
> 
> Mike
> 
> On Saturday 20 March 2010 17:53:24 Paragon Corporation wrote:
> > Mike,
> >
> > We apologize, didn't notice this was in a subquery and that you have a
> >  limit statement in your query.   So we presume regardless of your WHERE
> >  only 26 records are being selected.
> >
> > So Paul could be right that you do have data that is hitting some
> > compiled or variable limit.
> >
> > Does running the subquery alone work or you didn't try because it takes a
> > long time?
> >
> > Thanks,
> > Leo and Regina
> > http://www.postgis.us
> >
> > -----Original Message-----
> > From: Mike Leahy [mailto:mgl.gis at gmail.com] On Behalf Of Mike Leahy
> > Sent: Saturday, March 20, 2010 2:01 PM
> > To: Paragon Corporation
> > Cc: 'PostGIS Users Discussion'
> > Subject: Re: [postgis-users] ERROR: array size exceeds the maximum
> > allowed(134217727)
> >
> > Hi Leo/Regina,
> >
> > Thanks for the suggestion.  I tried adding the clause 'not geom is null'
> > to the where statement in each of the two sub-queries that have the
> > st_union(geom) functions are used, but it still segfaults.
> >
> > I also tried this on a fresh database with very little data, and it
> > doesn't seem to cause problems.  But I have two databases with live data
> > where I can cause this.  I have been able to pare one of these down to
> > remove personal information and reduce unnecessary data, while still
> > generating the crash with
> > that query.   Would someone be interested in a dump of this db?  Of
> > course, that someone would ideally be able to test this on a 64-bit
> > (K)ubutnu system, in the hopes that the problem can be replicated.
> >
> > Mike
> >
> > On Saturday 20 March 2010 11:32:35 Paragon Corporation wrote:
> > > Paul,
> > > I doubt array size limit is the issue.  He said when he left the where
> > > condition out it worked.  I would think it would definitely blow up in
> > > that case.
> > >
> > > Mike,
> > > The issue from before was that array aggregate functions did not
> > > handle NULLs correctly.  64-bit systems were more likely to segfault
> > > or give strange Errros in this case.
> > >
> > > To rule out that we still have some of these issues in the code base,
> > > can you add a
> > >
> > > geom IS NOT NULL
> > >
> > > Condition to your WHERE filter.  If that works, then the NULL issue is
> > > probably still lurking somewhere.
> > >
> > > Leo and Regina
> > > http://www.postgis.us
> > >
> > >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net
> > > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> > > Paul Ramsey
> > > Sent: Saturday, March 20, 2010 10:56 AM
> > > To: PostGIS Users Discussion; mgleahy at alumni.uwaterloo.ca
> > > Subject: Re: [postgis-users] ERROR: array size exceeds the maximum
> > > allowed(134217727)
> > >
> > > Actually, memunion does the opposite, it passes the resultant and
> > > preserves mem. The default behavior is fast-but-memory-hungry. And has
> > > been for some time, though in different forms. There were some bugs in
> > > the array handling code, but Mark CA killed most of them, so the
> > > latest 1.5 and 1.4 streams should be good. If it's possible that the
> > > issue is one of array size, maybe Mike could find the dial that
> > > controls that maximum, and turn it up and  down and see if it makes his
> >
> > problem go away/happen sooner.
> >
> > > P.
> > >
> > > On Sat, Mar 20, 2010 at 7:41 AM, strk <strk at keybit.net> wrote:
> > > > On Sat, Mar 20, 2010 at 05:49:42AM -0400, Mike Leahy wrote:
> > > >> Hello again,
> > > >>
> > > >> It might be of interest to point out that substituting st_union()
> > > >> with
> > > >> st_memunion() seems to have worked around this.  I'm curious
> > > >> though, because there is not a great deal of data being processed,
> > > >> and I am running this on a fairly sturdy system that that has more
> > > >> capacity than some of the Fedora systems I'm running.
> > > >
> > > > st_memunion builds a big array with all geometries in it..
> > > > you were hitting a limit of the array type.
> > > > st_union should behave better.
> > > >
> > > > --strk;
> > > >
> > > >  ()   Free GIS & Flash consultant/developer
> > > >  /\   http://strk.keybit.net/services.html
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
>