[postgis-users] ERROR: array size exceeds themaximumallowed(134217727)

Mike Leahy mgleahy at alumni.uwaterloo.ca
Sun Mar 21 16:43:02 PDT 2010 

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
> 
> <mgleahy at 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
> >> <mgleahy at 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-bounces at postgis.refractions.net
> >> >> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> >> >> Paragon Corporation
> >> >> Sent: Saturday, March 20, 2010 11:29 PM
> >> >> To: 'PostGIS Users Discussion'; mgleahy at 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
> >> >>-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-bounces at postgis.refractions.net
> >> >> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> >> >> Paul Ramsey
> >> >> Sent: Saturday, March 20, 2010 8:23 PM
> >> >> To: mgleahy at 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
> >> >> <mgleahy at 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.gis at 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-bounces at postgis.refractions.net
> >> >> >> > > [mailto:postgis-users-bounces at postgis.refractions.net] On
> >> >> >> > > Behalf Of Paul Ramsey
> >> >> >> > > Sent: Saturday, March 20, 2010 10:56 AM
> >> >> >> > > To: PostGIS Users Discussion; mgleahy at 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 <strk at 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 at postgis.refractions.net
> >> >> >> > > > http://postgis.refractions.net/mailman/listinfo/postgis-user
> >> >> >> > > >s
> >> >> >> > >
> >> >> >> > > _______________________________________________
> >> >> >> > > postgis-users mailing list
> >> >> >> > > postgis-users at postgis.refractions.net
> >> >> >> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >> >
> >> >> > _______________________________________________
> >> >> > postgis-users mailing list
> >> >> > postgis-users at postgis.refractions.net
> >> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >>
> >> >> _______________________________________________
> >> >> postgis-users mailing list
> >> >> postgis-users at postgis.refractions.net
> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >>
> >> >>
> >> >> _______________________________________________
> >> >> postgis-users mailing list
> >> >> postgis-users at postgis.refractions.net
> >> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at postgis.refractions.net
> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: crashing_smaller.sql
Type: text/x-sql
Size: 863 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100321/5917408c/attachment.bin>

More information about the postgis-users mailing list