As I was installing KUbuntu, I realized I should try the query on my own system, and under OS/X 10.6 I see the array error, though it seems to be cleanly trapped, there is no segfault.
crashdb=# \i ./crashing.sql psql:./crashing.sql:36: ERROR: array size exceeds the maximum allowed (134217727) crashdb=# P. On Sat, Mar 20, 2010 at 9:29 PM, Mike Leahy <mgle...@alumni.uwaterloo.ca> wrote: > Leo/Regina, > > In response to your earlier message, I did find that report of the identical > error message. However, as I'm sure you noticed, there is no apparent > investigation that followed after it. That was why I went to the PostgreSQL > IRC channel first...they walked me through generating a core dump, which is > what then pointed to it being a problem related to PostGIS. So while it's > certainly the same error being reported, the source of the problem in this > case appears PostGIS-specific - likely due to the logic that you note was > borrowed from the array_agg that caused the problem in the previous report. > > As for looking at the different query plans for working/non-working versions > of > the statement with different where conditions, I've attached several results > in > text files. Two examples that worked, one that segfaults, and one that > produces the 'array size exceeds...' error message. All of these are the same > query, just with variations in the where condition. The query plan is a but > much for me to interpret though - what does this look like to the experts? > > 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 > > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users