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

Sat Mar 20 20:28:41 PDT 2010

Mike,
Actually scanning thru the archives that 13... is a common number so suspect
that is an OS limit.

This could be very well a bug in PostgreSQL 8.4.    

Have you tried this on other PostgreSQL 8.4 installs on other your Fedoras -
you mentioned trying on older installs?

Your problem sounds exactly like this guy's and he wasn't using PostGIS at
all but array_agg (which is logic that PostGIS borrowed from to implement
the faster collecting ST_Union behavior)i
http://archives.postgresql.org/pgsql-hackers/2009-06/msg01171.php

If you haven't already probably good to check the plan difference between
the two queries (one with addtional WHERE and one without).  It could be
doing something nuts like running the sub select for each record you have in
the outer only when your extra WHERE condition is added and doing more or
less the sane thing when you take it out.

We've had some odd behavior in 8.4 with the planner doing strange things
that we haven't been able to pin down and had to implement workaround for.
As we described in this article.. Its probably unrelated though but just a
thought to throw out at the wind.

http://www.postgresonline.com/journal/index.php?/archives/149-Forcing-the-pl
anners-hand-with-set-enable_seqscan-off-WTF.html

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 8:23 PM
To: mgleahy at alumni.uwaterloo.ca; PostGIS Users Discussion
Subject: Re: [postgis-users] ERROR: array size exceeds the
maximumallowed(134217727)

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
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users