Hi, That query there does not crash. I'll try to follow up on your suggestions in the prior email.
Mike On Saturday 20 March 2010 23:35:32 Paragon Corporation wrote: > Mike, > Another thought. I suppose it could be the more or less two identical > subselects both with array_aggs > Just to give Paul something hopefully shorter to work with. Does this fail > too? > > SELECT csls.chat_id, csls.ugeom > FROM ( > > SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id > FROM testdb.user_selections AS us1 > INNER JOIN testdb.chat_selection_links AS csl1 ON us1.id = > csl1.selection_id > WHERE (not us1.user_drawing and not csl1.deleted) > GROUP BY chat_id > > ) AS csls > > LEFT JOIN ( > > SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id > FROM testdb.user_selections AS us2 > INNER JOIN testdb.chat_selection_links AS csl2 ON us2.id = > csl2.selection_id > WHERE (us2.user_drawing and us2.deleted and not csl2.deleted) > GROUP BY chat_id > > ) AS csld ON csls.chat_id = csld.chat_id > > 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 Paragon > Corporation > Sent: Saturday, March 20, 2010 11:29 PM > To: 'PostGIS Users Discussion'; mgle...@alumni.uwaterloo.ca > Subject: Re: [postgis-users] ERROR: array size exceeds > themaximumallowed(134217727) > > 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-p > l anners-hand-with-set-enable_seqscan-off-WTF.html > > 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 8:23 PM > To: mgle...@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 <mgle...@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....@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 > > _______________________________________________ > 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