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

[Mike Leahy]

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
>