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

[Paul Ramsey]

Mike

Well, if you can reduce it to a small db dump and query that exercises
it, I can install a 64bit ubuntu VM and see if it happens here too.

P

On Sat, Mar 20, 2010 at 3:30 PM, Mike Leahy <mgleahy at alumni.uwaterloo.ca> wrote:
> 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>