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....@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-boun...@postgis.refractions.net > > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > > Paul Ramsey > > Sent: Saturday, March 20, 2010 10:56 AM > > To: PostGIS Users Discussion; mgle...@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 <s...@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@postgis.refractions.net > > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
