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

Paul Ramsey pramsey at cleverelephant.ca
Sun Mar 21 15:28:59 PDT 2010 

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