[postgis-users] ERROR: array size exceeds themaximumallowed(134217727)
Paragon Corporation
lr at pcorp.us
Sun Mar 21 20:01:34 PDT 2010
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