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

Paul Ramsey pramsey at cleverelephant.ca
Mon Mar 22 09:45:33 PDT 2010 

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 <mgleahy at 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.gis at 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
>> >
>> > <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.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-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-u
>> > >> >> >> > > sers
>> > >> >> >
>> > >> >> > _______________________________________________
>> > >> >> > 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