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

Mike Leahy mgleahy at alumni.uwaterloo.ca
Sat Mar 20 21:04:00 PDT 2010 

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-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-the-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-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
>

More information about the postgis-users mailing list