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
