Did you already try replacing your postgis functions with array_agg calls to see if we can push the problem back over the fence to pgsql land?
http://www.postgresonline.com/journal/index.php?/archives/126-PostgreSQL-8.4-Faster-array-building-with-array_agg.html P. On Sun, Mar 21, 2010 at 8:54 PM, Mike Leahy <mgle...@alumni.uwaterloo.ca> wrote: > Leo/Regina, > > Yes, I can confirm that the non-working queries work when enable_nestloop is > off, and enable_hashjoin is on. > > The opposite is not the case - setting enable_nestloop on and enable_hashjoin > off does not make the 'working' variations of my query crash. > > Nice catch - is there anything further I can do from my end to troubleshoot > this? > > Mike > > > > > On Sunday 21 March 2010 23:01:34 Paragon Corporation wrote: >> Mike and Paul, >> We took a quick look at the query plans. Admittedly we suck at reading >> text plans; always rely on the graphical explain. >> >> Anyrate -- one striking thing it seems between the ones that don't work and >> the ones that work are >> >> The ones that work never employ a nested loop scan >> >> Both the segfault one and the exceed max allowed employ nested loops. So >> maybe its the interaction between nested loops and hash agg causing the >> problem. >> >> >> Could you two try disabling nested loop scan and run the crashing ones. >> Also verify that the plan has indeed changed to not use nested loops. >> >> set enable_nestloop = off; >> set enable_hashjoin = on; >> >> run query >> >> >> --- If you can't disable nested loop, try disabling the hash_join to see if >> you can make the working queries crash >> >> set enable_hashjoin = off; >> set enable_nestloop = on; >> run query >> >> >> Leo and Regina >> http://www.postgis.us >> >> -----Original Message----- >> From: Mike Leahy [mailto:mgl....@gmail.com] On Behalf Of Mike Leahy >> Sent: Sunday, March 21, 2010 7:43 PM >> To: Paul Ramsey >> Cc: PostGIS Users Discussion; Paragon Corporation >> Subject: Re: [postgis-users] ERROR: array size exceeds >> themaximumallowed(134217727) >> >> Ok, >> >> I've attached the smallest version of this query I can make before the >> error goes away. It seems to be very particular, but at least I have been >> able to take out some of the subqueries. Does this still produce the error >> in your environments as well? >> >> Mike >> >> On Sunday 21 March 2010 18:28:59 Paul Ramsey wrote: >> > Suggests nothing in particular, I'm afraid, because this isn't my area >> > of expertise ;) It's probably an 8-hour bug, so it could be a while >> > before I can devote an entire day to tracking back into it. It would >> > be ideal to have a smaller query that does the same thing. >> > >> > On Sun, Mar 21, 2010 at 12:58 PM, Mike Leahy >> > >> > <mgle...@alumni.uwaterloo.ca> wrote: >> > > Paul, >> > > >> > > Thanks for looking into it - at least I know it's not just me (for >> > > the array size limit at least). >> > > >> > > What does a cleanly trapped error like this suggest to you? If you >> > > try different variations on the where condition at the end of the >> > > statement, are you able to get a segfault? >> > > >> > > Mike >> > > >> > > On Sunday 21 March 2010 12:57:03 Paul Ramsey wrote: >> > >> 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.ph >> > >> >> p >> > >> >> >> > >> >> 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-For >> > >> >>cing -th e-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- >> > >> >> >> > > >user >> > >> >> >> > > >s >> > >> >> >> > > >> > >> >> >> > > _______________________________________________ >> > >> >> >> > > postgis-users mailing list >> > >> >> >> > > postgis-users@postgis.refractions.net >> > >> >> >> > > http://postgis.refractions.net/mailman/listinfo/postgis-u >> > >> >> >> > > sers >> > >> >> > >> > >> >> > _______________________________________________ >> > >> >> > 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
