[postgis-users] MULTIPOINT WKT - parentheses or no parentheses?
Hello list, Hello all - does anyone know about whether the multipoint WKT format should have points separated with parentheses (e.g., "MULTIPOINT((1 2),(3 4))"), or should it not (e.g., "MULTIPOINT(1 2,3 4)")? PostGIS returns the latter (no parentheses), but if you look at the OGC doc linked from here here: http://www.opengeospatial.org/standards/sfa, the example for MULTIPOINT on page 61 has parentheses - same thing on Wikipedia: http://en.wikipedia.org/wiki/Well-known_text - I'm not sure what the correct OGC reference for WKB is though. I'm asking because the WKT parser in OpenLayers currently expects parentheses when its parser reads a multipoint, which is easy enough to work around. But should PostGIS be including parentheses if it is the accepted format (or is there a way to do this already?), or should we expect that any application that parses WKT must be able to pars both formats (e.g., like PostGIS does)? Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Hi, Thanks again for the suggestions. The default values for join_collapse_limit and from_collapse_limit were set to 8 - I upped these both to 128, with no observable difference. I guess this issue is better suited for the general PostgreSQL mailing lists... Regards, Mike On Wednesday 31 March 2010 15:51:14 Paragon Corporation wrote: > Mike, > > Couple of thoughts. Given you have so many joins, could be you are > reaching the join collapse limit and the planner is kicking out before > making an optimal plan. > > Try increasing the join_collapse_limit and from_collapse_limit > > As was detailed in this thread > > http://archives.postgresql.org/pgsql-performance/2009-04/msg00258.php > > > > Alternatively could be your actual and estimated costs are out of wack and > might help upping your default targets and reanalyzing data. You can > probably get a sense of this by doing a an explain analyze of your query > and comparing the actual cost/row count with the estimated cost/row count > where its doing a nested loop. > > Admittedly this hasn't helped much for us. > > http://archives.postgresql.org/pgsql-performance/2009-02/msg00336.php > > Leo and Regina, > > http:///www.postgis.us > > > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mike > Leahy > Sent: Wednesday, March 31, 2010 1:17 PM > To: Mark Cave-Ayland > Cc: PostGIS Users Discussion > Subject: Re: [postgis-users] Nested loop join = very bad performance > > Hi Mark, > > I set effective_cache_size to 3072mb, and shared_buffers to 1024mb (as my > system has a total of 4gb). This only slightly (if at all) improves the > performance, maybe reducing the query by somewhere around 500 ms (down to > ~14700 ms). All other parameters in the postgresql.conf are defaults. I > don't recall exactly what I changed before (I was just tried increasing > memory limits and other things pretty much without knowing what I was > doing), but none of that really seems to have a significant impact on the > performance. > > The challenge with trying to reduce this query is that the nested loop join > only happens with the query as a whole (in general). The briefest example > I could put together was presented in the thread last week (see the > attachment here: > http://postgis.org/pipermail/postgis-users/2010-March/026239.html). If I > pull any more parameters or parts out of the query, the nest loop (and the > resulting errors/crashes I was encountering at the time) would not happen. > > Regards, > Mike > > On Wednesday 31 March 2010 04:40:09 Mark Cave-Ayland wrote: > > Mike Leahy wrote: > > > Mark/List, > > > > > > I just replaced my postgresql.conf with the default copy that > > > appears in /etc/postgresql/8.4/main/ after a fresh install. The > > > performance is pretty much the same as before (maybe even about 400 > > > ms worse than before). > > > > > > Is there anything else I should try? > > > > > > Mike > > > > Hi Mike, > > > > Which parameters did you change? effective_cache_size and > > shared_buffers should be tweaked to suit the RAM available in your > > machine but the rest of the defaults are fairly sensible. > > > > You probably want to set effective_cache_size to ~75% of your physical > > RAM and shared_buffers to ~25%. Does that make any difference at all? > > > > Otherwise, you'll need to start breaking down your query into parts to > > see which bit is causing the slowdown. Start with the innermost query > > and then add one join at a time until you find the part which is > > causing the slowdown. > > > > > > ATB, > > > > Mark. > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Hi Mark, I set effective_cache_size to 3072mb, and shared_buffers to 1024mb (as my system has a total of 4gb). This only slightly (if at all) improves the performance, maybe reducing the query by somewhere around 500 ms (down to ~14700 ms). All other parameters in the postgresql.conf are defaults. I don't recall exactly what I changed before (I was just tried increasing memory limits and other things pretty much without knowing what I was doing), but none of that really seems to have a significant impact on the performance. The challenge with trying to reduce this query is that the nested loop join only happens with the query as a whole (in general). The briefest example I could put together was presented in the thread last week (see the attachment here: http://postgis.org/pipermail/postgis-users/2010-March/026239.html). If I pull any more parameters or parts out of the query, the nest loop (and the resulting errors/crashes I was encountering at the time) would not happen. Regards, Mike On Wednesday 31 March 2010 04:40:09 Mark Cave-Ayland wrote: > Mike Leahy wrote: > > Mark/List, > > > > I just replaced my postgresql.conf with the default copy that appears in > > /etc/postgresql/8.4/main/ after a fresh install. The performance is > > pretty much the same as before (maybe even about 400 ms worse than > > before). > > > > Is there anything else I should try? > > > > Mike > > Hi Mike, > > Which parameters did you change? effective_cache_size and shared_buffers > should be tweaked to suit the RAM available in your machine but the rest > of the defaults are fairly sensible. > > You probably want to set effective_cache_size to ~75% of your physical > RAM and shared_buffers to ~25%. Does that make any difference at all? > > Otherwise, you'll need to start breaking down your query into parts to > see which bit is causing the slowdown. Start with the innermost query > and then add one join at a time until you find the part which is causing > the slowdown. > > > ATB, > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Mark/List, I just replaced my postgresql.conf with the default copy that appears in /etc/postgresql/8.4/main/ after a fresh install. The performance is pretty much the same as before (maybe even about 400 ms worse than before). Is there anything else I should try? Mike On Monday 29 March 2010 17:35:29 Mike Leahy wrote: > Hi Mark, > > I don't recall making any changes...at least not until the error I > encountered last week cropped up - but I think I reversed those changes (I > will double- check tonight). But I think I have encountered performance > problems with this query before on other systems - I had originally > dismissed it as hardware limitations I was dealing in my testing system at > the time. After recent hardware upgrades and after dealing with that > specific bug, that's when the slow performance of this query really stood > out. > > Mike > > On Monday 29 March 2010 07:53:50 Mark Cave-Ayland wrote: > > Mike Leahy wrote: > > > Is this something that should be looked into, or should I just > > > incorporate the workaround of disabling nested loop joins into my code? > > > > I find that generally PostgreSQL is fairly good at its estimates unless > > someone has already tried to tune the database. Have you changed any of > > the settings in postgresql.conf from their defaults? > > > > > > ATB, > > > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Hi Mark, I don't recall making any changes...at least not until the error I encountered last week cropped up - but I think I reversed those changes (I will double- check tonight). But I think I have encountered performance problems with this query before on other systems - I had originally dismissed it as hardware limitations I was dealing in my testing system at the time. After recent hardware upgrades and after dealing with that specific bug, that's when the slow performance of this query really stood out. Mike On Monday 29 March 2010 07:53:50 Mark Cave-Ayland wrote: > Mike Leahy wrote: > > Is this something that should be looked into, or should I just > > incorporate the workaround of disabling nested loop joins into my code? > > I find that generally PostgreSQL is fairly good at its estimates unless > someone has already tried to tune the database. Have you changed any of > the settings in postgresql.conf from their defaults? > > > ATB, > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Sorry all...here's the explain output I promised in my previous message...
On Sunday 28 March 2010 11:57:01 Mike Leahy wrote:
> Hello list,
>
> Following up from the bug that was successfully resolved last week
> (http://trac.osgeo.org/postgis/ticket/469), I've found that nested loop
> joins are actually causing very bad performance with my queries.
>
> Thanks to the observations of Leo and Regina in that discussion, I have
> found that I can work around this by setting enable_nestloop = off. Once
> that is specified, queries start working about as fast. With
> enable_nestloop = on, a query may take ~15000 ms, while enable_nestloop =
> off returns the same query in ~90 ms.
>
> I have attached a textfile showing the different 'explain analzye' results
> for the full query with/without nestloop enabled. You could also refer to
> the somewhat simplified queries I put together in discussion thread
> related to bug #469 (see
> http://postgis.refractions.net/pipermail/postgis-users/2010-
> March/026235.html for some examples).
>
> Is this something that should be looked into, or should I just incorporate
> the workaround of disabling nested loop joins into my code?
>
> Thanks,
> Mike
>
enable_nestloop = on:
QUERY PLAN
Sort (cost=260.10..260.10 rows=1 width=385) (actual time=15084.727..15084.730
rows=25 loops=1)
Sort Key: cl.id
Sort Method: quicksort Memory: 32kB
-> Limit (cost=260.07..260.08 rows=1 width=360) (actual
time=15084.700..15084.708 rows=25 loops=1)
-> Sort (cost=260.07..260.08 rows=1 width=360) (actual
time=15084.699..15084.704 rows=25 loops=1)
Sort Key: cl.id
Sort Method: top-N heapsort Memory: 32kB
-> Nested Loop Left Join (cost=212.58..260.06 rows=1
width=360) (actual time=68.629..15083.483 rows=204 loops=1)
Join Filter: (csl1.chat_id = cl.id)
-> Nested Loop Left Join (cost=159.35..202.70 rows=1
width=328) (actual time=68.070..10418.323 rows=204 loops=1)
Join Filter: (csl2.chat_id = cl.id)
-> Nested Loop Left Join (cost=99.82..134.88
rows=1 width=296) (actual time=7.868..18.477 rows=204 loops=1)
Join Filter: (cl.id = chat_log.parent_id)
-> Hash Left Join (cost=87.20..120.09 rows=1
width=264) (actual time=0.040..1.787 rows=204 loops=1)
Hash Cond: (cl.id = ctu.id)
Filter: (((ctu.num_target_users IS NULL)
AND (ctg.num_target_groups IS NULL)) OR ((ctu.num_target_users +
ctg.num_target_groups) = 0) OR cl.username)::text = 'admin'::text) OR
('admin'::text = ANY (ctu.target_users_array)) OR ('{admin,everyone}'::text[]
&& ctg.target_groups_array)) AND (cl."time" > '2010-03-28
10:54:44.382616-04'::timestamp with time zone)))
-> Hash Left Join (cost=43.60..63.86
rows=205 width=256) (actual time=0.030..1.302 rows=204 loops=1)
Hash Cond: (cl.id = ctg.id)
-> Seq Scan on chat_log cl
(cost=0.00..12.62 rows=205 width=184) (actual time=0.015..0.833 rows=204
loops=1)
Filter: ((NOT deleted) AND
(id < 5090) AND (depth = 0))
-> Hash (cost=41.10..41.10
rows=200 width=76) (actual time=0.004..0.004 rows=0 loops=1)
-> Subquery Scan ctg
(cost=34.60..41.10 rows=200 width=76) (actual time=0.004..0.004 rows=0 loops=1)
-> HashAggregate
(cost=34.60..39.10 rows=200 width=36) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on
chat_target_groups (cost=0.00..22.30 rows=1230 width=36) (actual
time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=41.10..41.10 rows=200
w
[postgis-users] Nested loop join = very bad performance
Hello list, Following up from the bug that was successfully resolved last week (http://trac.osgeo.org/postgis/ticket/469), I've found that nested loop joins are actually causing very bad performance with my queries. Thanks to the observations of Leo and Regina in that discussion, I have found that I can work around this by setting enable_nestloop = off. Once that is specified, queries start working about as fast. With enable_nestloop = on, a query may take ~15000 ms, while enable_nestloop = off returns the same query in ~90 ms. I have attached a textfile showing the different 'explain analzye' results for the full query with/without nestloop enabled. You could also refer to the somewhat simplified queries I put together in discussion thread related to bug #469 (see http://postgis.refractions.net/pipermail/postgis-users/2010- March/026235.html for some examples). Is this something that should be looked into, or should I just incorporate the workaround of disabling nested loop joins into my code? Thanks, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: array size exceeds themaximumallowed(134217727)
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@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 > > > > 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 > > >> > > > > > > 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 example
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 > > 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 > >> > > > > 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 > >> >> > >> >
Re: [postgis-users] ERROR: array size exceeds themaximumallowed(134217727)
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 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-boun...@postgis.refractions.net > >> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > >> Paragon Corporation > >> Sent: Saturday, March 20, 2010 11:29 PM > >> To: 'PostGIS Users Discussion'; mgle...@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 runni
Re: [postgis-users] ERROR: array size exceeds themaximumallowed(134217727)
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-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon > Corporation > Sent: Saturday, March 20, 2010 11:29 PM > To: 'PostGIS Users Discussion'; mgle...@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-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul > Ramsey > Sent: Saturday, March 20, 2010 8:23 PM > To: mgle...@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 > > 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
Re: [postgis-users] ERROR: array size exceeds themaximumallowed(134217727)
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-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon > Corporation > Sent: Saturday, March 20, 2010 11:29 PM > To: 'PostGIS Users Discussion'; mgle...@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-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul > Ramsey > Sent: Saturday, March 20, 2010 8:23 PM > To: mgle...@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 > > 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, >
Re: [postgis-users] ERROR: array size exceeds the maximum allowed(134217727)
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@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-boun...@postgis.refractions.net > > > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > > > Paul Ramsey > > > Sent: Saturday, March 20, 2010 10:56 AM > > > To: PostGIS Users Discussion; mgle...@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 wrote: >
Re: [postgis-users] ERROR: array size exceeds the maximum allowed(134217727)
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@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-boun...@postgis.refractions.net > > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > > Paul Ramsey > > Sent: Saturday, March 20, 2010 10:56 AM > > To: PostGIS Users Discussion; mgle...@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 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 hit
Re: [postgis-users] ERROR: array size exceeds the maximum allowed (134217727)
Chris/list: I have tried booting into the following kernels, all exhibiting the same issue: 2.6.31-20-generic 2.6.31-19-generic 2.6.31-14-generic I believe 2.6.31-14-generic is the original kernel installed with this version of Ubuntu, so unless the change to the kernel you're thinking occurred previous to the 9.10 Karmic release, it's not the culprit here. Mike On Saturday 20 March 2010 15:03:47 Mike Leahy wrote: > Chris, > > Sorry for not catching this one sooner (I'm subscribed to the digest)...but > thanks for pointing this out. The system is completely up to date. I could > reboot into an older kernel to see if that solve the problem. I'll report > results a bit later today. > > Mike > > -- > > Message: 7 > Date: Sat, 20 Mar 2010 10:03:13 -0700 > From: Chris Hermansen > Subject: Re: [postgis-users] ERROR: array size exceeds the maximum > allowed (134217727) > To: PostGIS Users Discussion > Message-ID: <4ba4ffd1.9020...@timberline.ca> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Very recently there was a kernel patch in Ubuntu regarding - if memory > serves - memory copying. How up to date is the system in question? > > strk wrote: > > On Sat, Mar 20, 2010 at 07:56:14AM -0700, Paul Ramsey wrote: > >> Actually, memunion does the opposite, it passes the resultant and > >> preserves mem. > > > > Oops, sorry for the confusion. Not-so-intuitive name :P > > > > --strk; > > > > () Free GIS & Flash consultant/developer > > /\ http://strk.keybit.net/services.html > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: array size exceeds the maximum allowed (134217727)
Chris, Sorry for not catching this one sooner (I'm subscribed to the digest)...but thanks for pointing this out. The system is completely up to date. I could reboot into an older kernel to see if that solve the problem. I'll report results a bit later today. Mike -- Message: 7 Date: Sat, 20 Mar 2010 10:03:13 -0700 From: Chris Hermansen Subject: Re: [postgis-users] ERROR: array size exceeds the maximum allowed (134217727) To: PostGIS Users Discussion Message-ID: <4ba4ffd1.9020...@timberline.ca> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Very recently there was a kernel patch in Ubuntu regarding - if memory serves - memory copying. How up to date is the system in question? strk wrote: > On Sat, Mar 20, 2010 at 07:56:14AM -0700, Paul Ramsey wrote: > >> Actually, memunion does the opposite, it passes the resultant and >> preserves mem. >> > > Oops, sorry for the confusion. Not-so-intuitive name :P > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
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-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul > Ramsey > Sent: Saturday, March 20, 2010 10:56 AM > To: PostGIS Users Discussion; mgle...@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 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@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: array size exceeds the maximum allowed (134217727)
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. Mike On Saturday 20 March 2010 05:17:50 Mike Leahy wrote: > Hello list, > > I have an interesting error that's coming up on an Ubuntu 64-bit system, > running PostgreSQL 8.4.2, and PostGIS 1.5.1 (packaged by me) as well as > 1.4.0 (from Ubuntu repos). The query itself is somewhat complex (I'm sure > it could be optimized better - but it's been working well for quite some > time). I've attached two files - one is a dummed-down version of the > query I am running (crashing.sql). After discussing this with some folks > in the postgresql IRC channel, it seems that the st_geom(), or something > PostGIS-related is causing the error. > > Running this query on various data will produce one of two results. One is > the error mentioned in the subject (ERROR: array size exceeds the maximum > allowed (134217727)). I can find very little information on this error. > The other outcome is that it often causes the PostgreSQL backend to > segfault (see gdboutput.txt). > > I can attest that this query works fine on many installations of PostgreSQL > 8.2+, and PostGIS 1.3+. The only main difference I can see in this case > where I'm getting errors and segfaults is that I'm running this on Ubuntu, > whereas all of my successful cases are on Fedora (7 through 12). > > If I replace the st_union() function in the query with accum(), it still > produces the error/segfaults. > > Also, strangely, if I remove one of the where conditions near the end of > the query (e.g., " AND (cl.depth = 0)"), it suddenly works fine without > any issues (except that I actually need that condition). > > Does this sort of problem look familiar to anyone? I can try to put > together an example if that would be of any use, but since the same > data/query works on other Fedora systems that I am running, I'm not sure > how easy it is to replicate. > > Regards, > Mike > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ERROR: array size exceeds the maximum allowed (134217727)
Hello list,
I have an interesting error that's coming up on an Ubuntu 64-bit system,
running PostgreSQL 8.4.2, and PostGIS 1.5.1 (packaged by me) as well as 1.4.0
(from Ubuntu repos). The query itself is somewhat complex (I'm sure it could
be optimized better - but it's been working well for quite some time). I've
attached two files - one is a dummed-down version of the query I am running
(crashing.sql). After discussing this with some folks in the postgresql IRC
channel, it seems that the st_geom(), or something PostGIS-related is causing
the error.
Running this query on various data will produce one of two results. One is
the error mentioned in the subject (ERROR: array size exceeds the maximum
allowed (134217727)). I can find very little information on this error. The
other outcome is that it often causes the PostgreSQL backend to segfault (see
gdboutput.txt).
I can attest that this query works fine on many installations of PostgreSQL
8.2+, and PostGIS 1.3+. The only main difference I can see in this case where
I'm getting errors and segfaults is that I'm running this on Ubuntu, whereas
all of my successful cases are on Fedora (7 through 12).
If I replace the st_union() function in the query with accum(), it still
produces the error/segfaults.
Also, strangely, if I remove one of the where conditions near the end of the
query (e.g., " AND (cl.depth = 0)"), it suddenly works fine without any issues
(except that I actually need that condition).
Does this sort of problem look familiar to anyone? I can try to put together
an example if that would be of any use, but since the same data/query works on
other Fedora systems that I am running, I'm not sure how easy it is to
replicate.
Regards,
Mike
SELECT tdesc.* FROM (
SELECT cl.id, cl.parent_id, cl.username, cl.type, cl.text, cl.time, cl.flags, cl.depth, '[stuff]' as extent, csls.selection, csld.drawing, round(date_part('epoch',time)*1000.0) AS epoch_ms, csls.selection, csld.drawing, ctu.target_users, ctg.target_groups, clr.replies, case when (num_target_users>0 or num_target_groups>0) then true else false end AS private FROM testdb.chat_log AS cl
LEFT JOIN (
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 ON csls.chat_id = cl.id
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 csld.chat_id = cl.id
LEFT JOIN (
SELECT chat_target_users.id, count(*) AS num_target_users, '[stuff]'::varchar AS target_users, ARRAY['stuff']::text[] AS target_users_array FROM testdb.chat_target_users GROUP BY id
) AS ctu ON cl.id = ctu.id
LEFT JOIN (
SELECT chat_target_groups.id, count(*) AS num_target_groups, '[stuff]'::varchar AS target_groups, ARRAY['stuff']::text[] AS target_groups_array FROM testdb.chat_target_groups GROUP BY id
) AS ctg ON cl.id = ctg.id
LEFT JOIN (
SELECT chat_log.parent_id AS id, '[stuff]' AS replies FROM testdb.chat_log GROUP BY parent_id
) AS clr ON cl.id = clr.id WHERE ((num_target_users is null and num_target_groups is null) or (num_target_users+num_target_groups)=0 or ((cl.username = 'admin' or 'admin' = any(target_users_array) or ARRAY['admin','everyone'] && target_groups_array) and (time>'2010-03-20 00:52:57.230968-04'::timestamptz))) AND (not deleted) AND (cl.depth = 0) ORDER BY cl.id desc LIMIT 25
) AS tdesc ORDER BY tdesc.id asc;GNU gdb (GDB) 7.0-ubuntu
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
For bug reporting instructions, please see:
Re: [postgis-users] undefined symbol GEOSPreparedCovers on Ubuntu 9.04
Hi Paul, Thanks for the tip. I had actually copied the source from another machine that I had compiled on (with a newer geos), and 'make clean' didn't do its job for some reason. A fresh source solved it...thanks. Mike (sorry for the off-list reply Paul) On September 18, 2009 12:58:37 Paul Ramsey wrote: > At root, your postgis is compiled against a more recent version of > GEOS than your system is finding. Track down that old geos and remove > it. > > P > > On Fri, Sep 18, 2009 at 9:47 AM, Mike Leahy wrote: > > Hello list, > > > > I've just attempted installing PostGIS 1.4.0 on Ubuntu 9.04 (i386). > > Everything seems to work okay up until I try loading PostGIS into a test > > database: > > > > postg...@mgleahy-dev:~$ psql -f > > /usr/share/postgresql/8.3/contrib/postgis.sql test > > BEGIN > > psql:/usr/share/postgresql/8.3/contrib/postgis.sql:53: NOTICE: type > > "spheroid" is not yet defined > > DETAIL: Creating a shell type definition. > > psql:/usr/share/postgresql/8.3/contrib/postgis.sql:53: ERROR: could not > > load library "/usr/lib/postgresql/8.3/lib/postgis-1.4.so": > > /usr/lib/postgresql/8.3/lib/postgis-1.4.so: undefined symbol: > > GEOSPreparedCovers > > > > A quick search on the web didn't turn up anything that looked applicable. > > Has anyone got an idea what might be wrong? > > > > Best regards, > > Mike > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] undefined symbol GEOSPreparedCovers on Ubuntu 9.04
Hello list, I've just attempted installing PostGIS 1.4.0 on Ubuntu 9.04 (i386). Everything seems to work okay up until I try loading PostGIS into a test database: postg...@mgleahy-dev:~$ psql -f /usr/share/postgresql/8.3/contrib/postgis.sql test BEGIN psql:/usr/share/postgresql/8.3/contrib/postgis.sql:53: NOTICE: type "spheroid" is not yet defined DETAIL: Creating a shell type definition. psql:/usr/share/postgresql/8.3/contrib/postgis.sql:53: ERROR: could not load library "/usr/lib/postgresql/8.3/lib/postgis-1.4.so": /usr/lib/postgresql/8.3/lib/postgis-1.4.so: undefined symbol: GEOSPreparedCovers A quick search on the web didn't turn up anything that looked applicable. Has anyone got an idea what might be wrong? Best regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Compiling 1.4.0 SVN
Hello list, I'm trying to compile the latest SVN snapshot, but I'm getting an error I haven't seen before. I'm on a relatively new F10 setup, which I haven't done much compiling on...so maybe I'm missing a requirement of some sort. But I'm able to configure/compile 1.3.5 without any obvious problem. Here's the output at the end: configure: creating ./config.status config.status: creating liblwgeom/Makefile config.status: creating liblwgeom/cunit/Makefile config.status: creating postgis/Makefile config.status: creating postgis/sqldefines.h config.status: creating loader/Makefile config.status: creating topology/Makefile config.status: creating regress/Makefile config.status: creating doc/Makefile config.status: creating postgis_config.h config.status: executing libtool commands sed: can't read ./ltmain.sh: No such file or directory sed: can't read ./ltmain.sh: No such file or directory mv: cannot stat `libtoolT': No such file or directory cp: cannot stat `libtoolT': No such file or directory chmod: cannot access `libtool': No such file or directory Any guesses about where I'm going wrong? Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_AsSVG crashes with large/complex geometries
Sorry...forgot the link: http://code.google.com/p/postgis/issues/detail?id=119 Mike Leahy wrote: Ok...the issue is posted here. There's sample geometries there if you want to give it a shot Marco. Mike Leahy wrote: Hi Mark, Sorry for the delay - I've been on the road a bit. I've got postgresql-8.3.6, and postgis-1.3.5, both installed from the Fedora 10 repos. I'll report this as a bug sometime in the next few days when I have a bit of time to put a sample together...unless anyone has any other suggestions? Mike Message: 22 Date: Thu, 19 Feb 2009 14:18:16 + From: Mark Cave-Ayland Subject: Re: [postgis-users] ST_AsSVG crashes with large/complex geometries To: mgle...@alumni.uwaterloo.ca,PostGIS Users Discussion Message-ID: <499d6a28.5080...@siriusit.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Mike Leahy wrote: > Hello list, > > I'm wondering if anyone has run into issues with st_assvg locking up > postgresql. I have an application that generates svg from unioned sets > of geometries. So far it has been working pretty well, except I just > tested it with very large sets of geometries, and it invariably locks up > the backend database without any errors etc. I could send a sample > geometry if that would help, but I think this can probably be done with > any dataset. I've verified that the problem is not with st_union, as it > outputs a geometry just fine...it's only when it gets to st_assvg does > it freeze things...there is no CPU activity. I do see this in the pg_log: > > *** glibc detected *** postgres: postgres testdb [local] SELECT: > realloc(): invalid next size: 0x0220f410 *** > > Am I just expecting too much of the st_assvg function, or is this > something that might be fixable? > > Thanks for any advice, > Mike Hi Mike, Yeah, that sounds like a bug. What versions of PostgreSQL/PostGIS are you using? You may want to open a bug with a sample geometry on the PostGIS bugtracker. ATB, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_AsSVG crashes with large/complex geometries
Ok...the issue is posted here. There's sample geometries there if you want to give it a shot Marco. Mike Leahy wrote: Hi Mark, Sorry for the delay - I've been on the road a bit. I've got postgresql-8.3.6, and postgis-1.3.5, both installed from the Fedora 10 repos. I'll report this as a bug sometime in the next few days when I have a bit of time to put a sample together...unless anyone has any other suggestions? Mike Message: 22 Date: Thu, 19 Feb 2009 14:18:16 + From: Mark Cave-Ayland Subject: Re: [postgis-users] ST_AsSVG crashes with large/complex geometries To: mgle...@alumni.uwaterloo.ca,PostGIS Users Discussion Message-ID: <499d6a28.5080...@siriusit.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Mike Leahy wrote: > Hello list, > > I'm wondering if anyone has run into issues with st_assvg locking up > postgresql. I have an application that generates svg from unioned sets > of geometries. So far it has been working pretty well, except I just > tested it with very large sets of geometries, and it invariably locks up > the backend database without any errors etc. I could send a sample > geometry if that would help, but I think this can probably be done with > any dataset. I've verified that the problem is not with st_union, as it > outputs a geometry just fine...it's only when it gets to st_assvg does > it freeze things...there is no CPU activity. I do see this in the pg_log: > > *** glibc detected *** postgres: postgres testdb [local] SELECT: > realloc(): invalid next size: 0x0220f410 *** > > Am I just expecting too much of the st_assvg function, or is this > something that might be fixable? > > Thanks for any advice, > Mike Hi Mike, Yeah, that sounds like a bug. What versions of PostgreSQL/PostGIS are you using? You may want to open a bug with a sample geometry on the PostGIS bugtracker. ATB, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_AsSVG crashes with large/complex geometries
Hi Mark, Sorry for the delay - I've been on the road a bit. I've got postgresql-8.3.6, and postgis-1.3.5, both installed from the Fedora 10 repos. I'll report this as a bug sometime in the next few days when I have a bit of time to put a sample together...unless anyone has any other suggestions? Mike Message: 22 Date: Thu, 19 Feb 2009 14:18:16 + From: Mark Cave-Ayland Subject: Re: [postgis-users] ST_AsSVG crashes with large/complex geometries To: mgle...@alumni.uwaterloo.ca,PostGIS Users Discussion Message-ID: <499d6a28.5080...@siriusit.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Mike Leahy wrote: > Hello list, > > I'm wondering if anyone has run into issues with st_assvg locking up > postgresql. I have an application that generates svg from unioned sets > of geometries. So far it has been working pretty well, except I just > tested it with very large sets of geometries, and it invariably locks up > the backend database without any errors etc. I could send a sample > geometry if that would help, but I think this can probably be done with > any dataset. I've verified that the problem is not with st_union, as it > outputs a geometry just fine...it's only when it gets to st_assvg does > it freeze things...there is no CPU activity. I do see this in the pg_log: > > *** glibc detected *** postgres: postgres testdb [local] SELECT: > realloc(): invalid next size: 0x0220f410 *** > > Am I just expecting too much of the st_assvg function, or is this > something that might be fixable? > > Thanks for any advice, > Mike Hi Mike, Yeah, that sounds like a bug. What versions of PostgreSQL/PostGIS are you using? You may want to open a bug with a sample geometry on the PostGIS bugtracker. ATB, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_AsSVG crashes with large/complex geometries
Hello list, I'm wondering if anyone has run into issues with st_assvg locking up postgresql. I have an application that generates svg from unioned sets of geometries. So far it has been working pretty well, except I just tested it with very large sets of geometries, and it invariably locks up the backend database without any errors etc. I could send a sample geometry if that would help, but I think this can probably be done with any dataset. I've verified that the problem is not with st_union, as it outputs a geometry just fine...it's only when it gets to st_assvg does it freeze things...there is no CPU activity. I do see this in the pg_log: *** glibc detected *** postgres: postgres testdb [local] SELECT: realloc(): invalid next size: 0x0220f410 *** Am I just expecting too much of the st_assvg function, or is this something that might be fixable? Thanks for any advice, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Strange behaviour with st_difference
Mark Cave-Ayland wrote: Mike Leahy wrote: Hello list, I'm having a problem with st_difference, where two overlapping geometries will return an empty geometrycollection, as if the do not overlap. However, if I use st_difference on the same geometries, but converted to EWKT, then back, then using st_difference yields the expected output. Basically, this is what I'm getting (minus the output coords): # select st_astext(st_difference(g1,g2)), st_astext(st_difference(st_asewkt(g1),st_asewkt(g2))) from temp; st_astext | st_astext --+--- GEOMETRYCOLLECTION EMPTY | POLYGON(( )) (1 row) The resulting (correct) polygon is very small (a total area of 0.00048828125, using UTM coordinates), so maybe there's a problem with that? The result of st_overlaps(g1,g2) is true for the same data that cause this problem. I could email someone the geometries if that would help. Regards, Mike Hi Mike, I think you'll have to post the geometries if you want someone to help you. If they are large then place them in a text file and send it to the list as a zipped/gzipped attachment. Oh, and don't forget the output of "SELECT postgis_full_version()" while you're at it :) ATB, Mark. Here they are attached - both as text and as binary within an sql statement that replicates the results of the example describe above. postgis_full_version (from the yum repo on Fedora 10, x86_64): postgis_full_version - POSTGIS="1.3.5" GEOS="3.0.3-CAPI-1.4.2" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS g1g2.sql.gz Description: GNU Zip compressed data ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Strange behaviour with st_difference
Hello list, I'm having a problem with st_difference, where two overlapping geometries will return an empty geometrycollection, as if the do not overlap. However, if I use st_difference on the same geometries, but converted to EWKT, then back, then using st_difference yields the expected output. Basically, this is what I'm getting (minus the output coords): # select st_astext(st_difference(g1,g2)), st_astext(st_difference(st_asewkt(g1),st_asewkt(g2))) from temp; st_astext | st_astext --+--- GEOMETRYCOLLECTION EMPTY | POLYGON(( )) (1 row) The resulting (correct) polygon is very small (a total area of 0.00048828125, using UTM coordinates), so maybe there's a problem with that? The result of st_overlaps(g1,g2) is true for the same data that cause this problem. I could email someone the geometries if that would help. Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Re: Restoring db with indexes using PostGIS functions
Regina, I'm using 8.3.3 (and 8.3.4 on another machine). I'm not quite sure this is a bug with pg_dump...though maybe it is. From what I can tell, the reason that constraints are not a problem (i.e., those typically created by the addgeometrycolumn() function) are because they generally use geometrytype(), not st_geometrytype() - the latter calls the former. So in my dump file, for the constraints, I'll see something like this in a table's definition: CONSTRAINT enforce_geotype_extent CHECK (((public.geometrytype(extent) = 'POLYGON'::text) OR (extent IS NULL))), This is fine. But for the indexes that I had created, I have the following pairs of statements, where 'mytable' is in 'myschema': SET search_path = myschema, pg_catalog; CREATE INDEX mytable_geomtype_idx ON mytable USING btree (public.st_geometrytype(geom)); It seems that pg_dump knows to put the 'public' schema in front of the function names in the constraint or index statements. However, the 'CREATE FUNCTION' statement for the st_geometrytype() function does not refer to public.geometrytype(), but only geometrytype(). I think this is where the problem lies - because once the search path is set to something other than 'public', then executing public.st_geometrytype() causes an error since it can no longer find geometrytype() in the current schema. To make a long story short, if I edit the st_geometrytype() function in my dump file so that it points to public.geometrytype(), then the problem I encountered goes away. If we assume pg_dump is at fault here, then either it's strategy would have to be rewritten so that it explicitly includes schema names instead of setting the search path throughout the output script (I don't imagine I could make a persuasive argument for this to the PostgreSQL developers), or it has to somehow know to add the 'public' schema to any references to functions inside other functions, such as in st_geometrytype() (should it actually be able to do this?). Alternatively, PostGIS could have explicit schema references where any functions are referenced inside other functions. My guess is this might be the simpler solution. Though for my case, now that I understand the problem, it's easy to work around. Thanks for your help, Mike Mike, I wonder if it's a bug in PostgreSQL 8.3. I'm running both 8.2.5 and 8.3. 8.2.5 on a Windows 2003 and 8.3.3 on Linux. Which point version of 8.3 are you running? I vaguely recall a couple of fixes being done to the dump restore between 8.3.0 and 8.3.3, but I could be wrong. Anyrate sounds like a pg_dump bug. I wouldn't notice your particular problem since all my indexes are gist indexes which are in pg_catalog anyway and I don't think I have any btree indexes I can think of that use public functions. They all use pg_catalog functions or no functions. Its strange constraints are not an issue and indexes are. I would have thought the same problem would arise. Regina ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Re: Restoring db with indexes using PostGIS functions
Hello Regina, There's no problems with table constraints or anything like that...the only issue is with the indexes that use st_geometrytype. I can give the compressed format a try...what version of PostgreSQL are you using? I've got 8.3 on the machines I'm using, and I think the dump format has changed slightly from one version to the next (though I'm not 100% sure about that). I don't normally set the search path either. As you recommend, I explicitly reference the schema.table names in all of the SQL that I've been writing myself. It's only when restoring the database that I've encountered this. Mike Paragon Corporation wrote: Mike, I'm a bit puzzled about your problem. Are you saying that you use multiple schemas for tables and have your postgis functions in public and when you restore your indexes they are not being created because of the search path thing. I use multiple table schemas too and as far as I can remember all my spatial constraints and indexes come back and I don't see any prefixing of public in the functions. Although sounds like you are dumping to sql format and I always dump to compressed format. Perhaps they behave differently. Is it just your indexes that are missing or the constraints as well? Your indexes seem a little out of the ordinary, but I would expect you would have the same issue With postgis generated constraints. Do your table indexes actually show without the schema qualification on them when you look at them. Mine always show as CREATE INDEX sometable_idx ON someschema.sometable USING btree (ST_GeometryType(the_geom)); But then again I always explicitly put in the schema prefix when creating indexes. How would you be able to create the indexes otherwise unless you are setting the search path before you create your indexes Set search_path = schemaname CREATE INDEX ... ON sometable Is that how you create your indexes or do you explicitly reference the table schema name in the CREATE? Hope that helps, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Leahy Sent: Tuesday, September 30, 2008 4:00 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Re: Restoring db with indexes using PostGIS functions Mark Cave-Ayland wrote: Mike Leahy wrote: Hello list, I ran into a problem today when restoring a database of mine - I'm not sure if this is anything new, or if I'm doing something I shouldn't be doing. I have in my db a variety of tables that are stored in separate schemas. Some of these tables have geometry columns that I am using to store variable geometry types, but since most of the queries on these tables generally focus on one geometry type at a time, I added indexes to them as follows: CREATE INDEX mytable_geomtype_idx ON schemaname.mytable USING btree (st_geometrytype(geom)); However, when I dump the database, the resulting sql sets search paths rather than explicitly naming schema.table in each statement. Effectively, this is what happens when the dumped sql is loaded into a new database: mydb=# SET search_path = schemaname, pg_catalog; SET mydb=# CREATE INDEX mytable_geomtype_idx ON mytable USING btree (public.st_geometrytype(geom)); ERROR: function geometrytype(public.geometry) does not exist LINE 1: SELECT geometrytype( $1 ) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT geometrytype( $1 ) CONTEXT: PL/pgSQL function "st_geometrytype" line 6 during statement block local variable initialization mydb=# It seems that setting the search path has undesirable effects with the st_geometrytype function (I don't know if the same happens with other functions). I suspect 'geometrytype( $1 )' would have to be substituted with 'public.geometrytype( $1 )' for this to work. Is this just something I should be aware of? It's not a show-stopper for me at the moment, but it means that I'd have to manually recreate any indexes like the one above if performance became an issue after restoring the database (not in my current situation, but maybe down the road). Regards, Mike Hi Mike, If you are using newer PostgreSQL releases, there was an issue a while back where the use of search_path within stored procedures was restricted to stop people from overriding in-built stored procedures. Otherwise it could simply be that the dump ordering is wrong, and altering the restore order so that geometrytype() is restored *before* ST_geometrytype should resolve the issue. HTH, Mark. Hello Mark, I don't think it is a problem with the order of restoration of the geometrytype() vs. st_geometrytype() functions, because those are loaded and working fine before anything else happens in the restore script. The problem is w
[postgis-users] Re: Restoring db with indexes using PostGIS functions
Mark Cave-Ayland wrote: Mike Leahy wrote: > Hello list, > > I ran into a problem today when restoring a database of mine - I'm not > sure if this is anything new, or if I'm doing something I shouldn't be > doing. I have in my db a variety of tables that are stored in separate > schemas. Some of these tables have geometry columns that I am using to > store variable geometry types, but since most of the queries on these > tables generally focus on one geometry type at a time, I added indexes > to them as follows: > > CREATE INDEX mytable_geomtype_idx ON schemaname.mytable USING btree > (st_geometrytype(geom)); > > However, when I dump the database, the resulting sql sets search paths > rather than explicitly naming schema.table in each statement. > Effectively, this is what happens when the dumped sql is loaded into a > new database: > > mydb=# SET search_path = schemaname, pg_catalog; > SET > mydb=# CREATE INDEX mytable_geomtype_idx ON mytable USING btree > (public.st_geometrytype(geom)); > ERROR: function geometrytype(public.geometry) does not exist > LINE 1: SELECT geometrytype( $1 ) > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > QUERY: SELECT geometrytype( $1 ) > CONTEXT: PL/pgSQL function "st_geometrytype" line 6 during statement > block local variable initialization > mydb=# > > It seems that setting the search path has undesirable effects with the > st_geometrytype function (I don't know if the same happens with other > functions). I suspect 'geometrytype( $1 )' would have to be substituted > with 'public.geometrytype( $1 )' for this to work. > > Is this just something I should be aware of? It's not a show-stopper > for me at the moment, but it means that I'd have to manually recreate > any indexes like the one above if performance became an issue after > restoring the database (not in my current situation, but maybe down the > road). > > Regards, > Mike Hi Mike, If you are using newer PostgreSQL releases, there was an issue a while back where the use of search_path within stored procedures was restricted to stop people from overriding in-built stored procedures. Otherwise it could simply be that the dump ordering is wrong, and altering the restore order so that geometrytype() is restored *before* ST_geometrytype should resolve the issue. HTH, Mark. Hello Mark, I don't think it is a problem with the order of restoration of the geometrytype() vs. st_geometrytype() functions, because those are loaded and working fine before anything else happens in the restore script. The problem is when indexes are being restored for the tables inside my schemas - the dump script will set the search path for one schema, create the indexes, then repeat for any other schemas. It does this throughout from what I can tell. This problem, from what I can tell, is not limited to the restore scripts. The problem occurs just by setting the search path to something other than 'public', then calling public.st_geometrytype(), which in turn calls geometrytype(). Since st_geometrytype() isn't calling public.geometrytype(), it can't find the function in the current search path. At least, this is what I think the issue is. The only way for me to get around this as things are now would be to edit the restore script so that it uses 'public' for the search path, then rewrite all the create index statements to refer to schema.table. Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Restoring db with indexes using PostGIS functions
Hello list, I ran into a problem today when restoring a database of mine - I'm not sure if this is anything new, or if I'm doing something I shouldn't be doing. I have in my db a variety of tables that are stored in separate schemas. Some of these tables have geometry columns that I am using to store variable geometry types, but since most of the queries on these tables generally focus on one geometry type at a time, I added indexes to them as follows: CREATE INDEX mytable_geomtype_idx ON schemaname.mytable USING btree (st_geometrytype(geom)); However, when I dump the database, the resulting sql sets search paths rather than explicitly naming schema.table in each statement. Effectively, this is what happens when the dumped sql is loaded into a new database: mydb=# SET search_path = schemaname, pg_catalog; SET mydb=# CREATE INDEX mytable_geomtype_idx ON mytable USING btree (public.st_geometrytype(geom)); ERROR: function geometrytype(public.geometry) does not exist LINE 1: SELECT geometrytype( $1 ) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT geometrytype( $1 ) CONTEXT: PL/pgSQL function "st_geometrytype" line 6 during statement block local variable initialization mydb=# It seems that setting the search path has undesirable effects with the st_geometrytype function (I don't know if the same happens with other functions). I suspect 'geometrytype( $1 )' would have to be substituted with 'public.geometrytype( $1 )' for this to work. Is this just something I should be aware of? It's not a show-stopper for me at the moment, but it means that I'd have to manually recreate any indexes like the one above if performance became an issue after restoring the database (not in my current situation, but maybe down the road). Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query that locks up system memory/disk usage
Kevin, Perhaps I can cause the same situation (maybe tomorrow when the server isn't occupied) and give this a try. But I think I have already ruled this out as an option. When I would hit ctrl+c in the psql terminal, I'd see a message that the query was being cancelled...then it would just sit there and continue grinding away - unless there's a possibility that maybe the ctrl+c command didn't actually reach the postgres process, my guess is that maybe the attempt to rollback gracefully is just as intensive. Mike Kevin Neufeld wrote: Paul, while "kill -9" will undoubtedly stop the current running query, it will also crash the entire database cluster since the shared memory will become corrupt. I recommend using a "kill -2" instead which is the same thing as issuing a ctrl^c while in the terminal program. It may take longer since it has to rollback the transaction, but it will do so gracefully. -- Kevin Paul Ramsey wrote: Break yourself of the subquery habit: select a.* from a join b on (st_dwithin(a.the_geom,b.the_geom,5)) where b.gid = 10; On your process: ps ax | grep postgres Find the process id that is using all the CPU and just kill -9 it. The glory of running a proper ACID database like PgSQL is that if you don't like what it's doing, you can rip the power cord out of the wall, and it'll still start up clean. (Do not try this with MySQL.) P. buffer((select b.the_geom where gid = 10),50000)); On Mon, Jul 14, 2008 at 4:33 PM, Mike Leahy wrote: Hello list, I've run into some situations where running certain queries end up locking up all of my system's ram memory, with constant disk access. I can't cancel the query by hitting ctrl+c in the psql terminal, by restarting the service, or even killing the postmaster. I'm running on a fairly high end system, so it's not an issue with CPU power or available ram. Here's an example of what I did today that caused this: Table A is a table I imported from a tile index shapefile. Table B has several fairly large irregular polygons of different study areas. To get all of the polygons in Table A within a certain distance (50km) of one of the polygons in Table B, without giving it much thought I did the following: select * from a where st_intersects(a.the_geom,buffer((select b.the_geom where gid = 10),5)); I realize how wrong that is, as calculates the buffer for every tile it compares to...I should have done something like: select * from a where st_intersects(a.the_geom,(select b.the_geom where gid = 10)) or st_distance(a.the_geom,(select b.the_geom where gid = 10))<=5; The problem is...I'm still waiting for the first query to either finish, or cancel, or something. In the meantime, postmaster is still using 99% of my memory, and the disk is still thrashing away (though CPU usage pretty much at 0). What's the best strategy to kill the previous query without having to shut down the entire server? Keep in mind that is just an example of how this can happen for me - I've had it happen in other more complex situations where it was less obvious what I was doing wrong in the logic of the query. I'm just wondering how I can recover from these sorts of mistakes without potentially damaging the database. Regards, Mike ___ 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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query that locks up system memory/disk usage
Great, Thanks for the help. Mike Paul Ramsey wrote: Break yourself of the subquery habit: select a.* from a join b on (st_dwithin(a.the_geom,b.the_geom,5)) where b.gid = 10; On your process: ps ax | grep postgres Find the process id that is using all the CPU and just kill -9 it. The glory of running a proper ACID database like PgSQL is that if you don't like what it's doing, you can rip the power cord out of the wall, and it'll still start up clean. (Do not try this with MySQL.) P. buffer((select b.the_geom where gid = 10),5)); On Mon, Jul 14, 2008 at 4:33 PM, Mike Leahy <[EMAIL PROTECTED]> wrote: Hello list, I've run into some situations where running certain queries end up locking up all of my system's ram memory, with constant disk access. I can't cancel the query by hitting ctrl+c in the psql terminal, by restarting the service, or even killing the postmaster. I'm running on a fairly high end system, so it's not an issue with CPU power or available ram. Here's an example of what I did today that caused this: Table A is a table I imported from a tile index shapefile. Table B has several fairly large irregular polygons of different study areas. To get all of the polygons in Table A within a certain distance (50km) of one of the polygons in Table B, without giving it much thought I did the following: select * from a where st_intersects(a.the_geom,buffer((select b.the_geom where gid = 10),5)); I realize how wrong that is, as calculates the buffer for every tile it compares to...I should have done something like: select * from a where st_intersects(a.the_geom,(select b.the_geom where gid = 10)) or st_distance(a.the_geom,(select b.the_geom where gid = 10))<=5; The problem is...I'm still waiting for the first query to either finish, or cancel, or something. In the meantime, postmaster is still using 99% of my memory, and the disk is still thrashing away (though CPU usage pretty much at 0). What's the best strategy to kill the previous query without having to shut down the entire server? Keep in mind that is just an example of how this can happen for me - I've had it happen in other more complex situations where it was less obvious what I was doing wrong in the logic of the query. I'm just wondering how I can recover from these sorts of mistakes without potentially damaging the database. Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Query that locks up system memory/disk usage
Hello list, I've run into some situations where running certain queries end up locking up all of my system's ram memory, with constant disk access. I can't cancel the query by hitting ctrl+c in the psql terminal, by restarting the service, or even killing the postmaster. I'm running on a fairly high end system, so it's not an issue with CPU power or available ram. Here's an example of what I did today that caused this: Table A is a table I imported from a tile index shapefile. Table B has several fairly large irregular polygons of different study areas. To get all of the polygons in Table A within a certain distance (50km) of one of the polygons in Table B, without giving it much thought I did the following: select * from a where st_intersects(a.the_geom,buffer((select b.the_geom where gid = 10),5)); I realize how wrong that is, as calculates the buffer for every tile it compares to...I should have done something like: select * from a where st_intersects(a.the_geom,(select b.the_geom where gid = 10)) or st_distance(a.the_geom,(select b.the_geom where gid = 10))<=5; The problem is...I'm still waiting for the first query to either finish, or cancel, or something. In the meantime, postmaster is still using 99% of my memory, and the disk is still thrashing away (though CPU usage pretty much at 0). What's the best strategy to kill the previous query without having to shut down the entire server? Keep in mind that is just an example of how this can happen for me - I've had it happen in other more complex situations where it was less obvious what I was doing wrong in the logic of the query. I'm just wondering how I can recover from these sorts of mistakes without potentially damaging the database. Regards, Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [Plr-general] [postgis-users] Pl/R
Just to make sure nobody's waiting on me for anything...I did give this a try a few weeks ago. At the moment, I can't get it to compile against PostgreSQL 8.3 on windows, so I haven't updated the installer (that's all I was really doing anyway). Whatever the problem is within the code is beyond my current skill/available time to fix. I think Joe Conway may be working on a fix, but there's no anticipated date that he will have it ready as far as I know. Maybe someone else might be able to offer some help? If you really need something that works on windows, and if you're not dependent on your current PostgreSQL installation, then you may be just as well off to go with PostgreSQL 8.2, and use PostGIS & PL/R with that...at least for the time being until PL/R is updated. Mike Obe, Regina wrote: George, Unfortunately I don't think so. I think Mike Leahy was working on one, but I think he is still working out some outstanding issues with it. Anyrate I am cc'ing plr group so perhaps someone can provide an update. Hope that helps, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George R. C. Silva Sent: Friday, May 02, 2008 1:56 PM To: PostGIS Users Discussion Subject: [postgis-users] Pl/R Hello everyone. I was following bostongis website (http://www.bostongis.com/blog/) and tried to install PL/R in my machine. (Windows XP - i know, i know).. My postgres version is 8.3. I couldnt install it. Is there a version out there for 8.3? Thanks ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ___ Plr-general mailing list [EMAIL PROTECTED] http://pgfoundry.org/mailman/listinfo/plr-general ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_union says geometries have different srids
Hello list,
I seem to be having trouble with st_union as an aggregate function.
Sorry if this is a known issue, but a quick search didn't turn anything
up similar. Below is an example where I have two polygons next to each
other
test=# create table test (gid serial primary key);
test=# select
addgeometrycolumn('public','test','test_geom',4326,'MULTIPOLYGON',2);
test=# insert into test (test_geom) values ('srid=4326;MULTIPOLYGON(((1
1,1 2,2 2,2 1,1 1)))');
test=# insert into test (test_geom) values ('srid=4326;MULTIPOLYGON(((2
1,3 1,3 2,2 2,2 1)))');
The following will fail no matter what shapes/srid I'm using:
test=# select st_union(test_geom) from test;
ERROR: Operation on mixed SRID geometries
But this works fine:
test=# select st_memunion(test_geom) from test;
st_memunion
010320E61001...
Is there something in particular that I'm doing wrong? This only seems
to be an issue with st_union as an aggregate, if I use
st_union(geometry,geometry), the result looks fine. This is on a
Fedora-7 (x86_64) machine with PostgreSQL 8.3, PostGIS 1.3.2,
Proj-4.5.0, GEOS-3.0.0 - I have a similar setup on another machine
running F8-x86_64 (except for Proj-4.6.0), and the failure is worse (the
postgresql server segfaults with 'signal 11'). Would this be a bug, or
have I committed some kind of user error? Is there anything I should
try to figure out where the source of my problem is?
Thanks in advance for any suggestions.
Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] A function for using PL/R with deldir to produce voronoi polygons in PostGIS
All,
Attached is a PL/R-based function that I put together based on Jan
Hartmann's help in the PotGIS list about a year ago for generating
voronoi polygons using the deldir library. Now that I've started with
PL/R, I was able capture this in a single function that will take in the
points from a table or query, produce the voronoi polygons, and return a
list of the polygon geometries along with the id's of their originating
points.
I'm not sure if the approach I used in this example is ideal; it hasn't
been tested on large datasets yet, and I'm sure someone can probably
find a different way to do this (there is also the tripack library in R,
which I haven't tried yet). However, it seems to work fine from what I
can tell. I just thought this might serve as an example of how PL/R can
be handy alongside PostGIS.
Regards,
Mike
P.S.: I did notice the same problem as Regina - once I started trying to
execute this script directly from the text file, I had to make sure it
was in UNIX format for PL/R to parse it properly...even though
everything I'm doing is on Windows at the moment.
--This function uses the deldir library in R to generate voronoi polygons for
an input set of points in a PostGIS table.
--Requirements:
-- R-2.5.0 with deldir-0.0-5 installed
-- PostgreSQL-8.2.x with PostGIS-1.x and PL/R-8.2.0.4 installed
--Usage: select * from voronoi('table','point-column','id-column');
--Where:
-- 'table' is the table or query containing the points to be used for
generating the voronoi polygons,
-- 'point-column' is a single 'POINT' PostGIS geometry type (each point
must be unique)
-- 'id-column' is a unique identifying integer for each of the originating
points (e.g., 'gid')
--Output: returns a recordset of the custom type 'voronoi', which contains the
id of the
-- originating point, and a polygon geometry
drop function voronoi(text,text,text);
drop type voronoi;
create type voronoi as (id integer, polygon geometry);
create or replace function voronoi(text,text,text) returns setof voronoi as '
library(deldir)
# select the point x/y coordinates into a data frame...
points <- pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from
%1$s;",arg1,arg2))
# calculate an approprate buffer distance (~10%):
buffer =
((abs(max(points$x)-min(points$x))+abs(max(points$y)-min(points$y)))/2)*(0.10)
# get EWKB for the overall buffer of the convex hull for all points:
buffer <- pg.spi.exec(sprintf("select
buffer(convexhull(geomunion(%2$s)),%3$.6f) as ewkb from
%1$s;",arg1,arg2,buffer))
# the following use of deldir uses high precision and digits to prevent
slivers between the output polygons, and uses
# a relatively large bounding box with four dummy points included to
ensure that points in the peripheral areas of the
# dataset are appropriately enveloped by their corresponding polygons:
voro =
deldir(points$x,points$y,digits=22,frac=0.01,list(ndx=2,ndy=2),rw=c(min(points$x)-abs(min(points$x)-max(points$x)),max(points$x)+abs(min(points$x)-max(points$x)),min(points$y)-abs(min(points$y)-max(points$y)),max(points$y)+abs(min(points$y)-max(points$y
tiles = tile.list(voro)
poly = array()
id = array()
p = 1
for (i in 1:length(tiles)) {
tile = tiles[[i]]
curpoly = "POLYGON(("
for (j in 1:length(tile$x)) {
curpoly = sprintf("%s %.6f
%.6f,",curpoly,tile$x[[j]],tile$y[[j]])
}
curpoly = sprintf("%s %.6f
%.6f))",curpoly,tile$x[[1]],tile$y[[1]])
# this bit will find the original point that corresponds to the
current polygon, along with its id and the SRID used for the
# point geometry (presumably this is the same for all
points)...this will also filter out the extra polygons created for the
# four dummy points, as they will not return a result from this
query:
ipoint <- pg.spi.exec(sprintf("select %3$s as id,
intersection(''SRID=''||srid(%2$s)||'';%4$s'',''%5$s'') as polygon from %1$s
where
intersects(%2$s,''SRID=''||srid(%2$s)||'';%4$s'');",arg1,arg2,arg3,curpoly,buffer$ewkb[1]))
if (length(ipoint) > 0)
{
poly[[p]] <- ipoint$polygon[1]
id[[p]] <- ipoint$id[1]
p = (p + 1)
}
}
return(data.frame(id,poly))
' language 'plr';
--An example of how this function can be used:
--create a table with some random (but unique) points:
create table geo (id serial primary key);
select addgeometrycolumn('','geo','pt',4326,'POINT',2);
insert into geo (pt) values ('SRID=4326;POINT(1 -4)');
insert into geo (pt) values ('SRID=4326;POINT(3.5 5)');
insert into ge
Re: [postgis-users] Combined Windows version of PostGIS/P-LR
That might have something to do with it. I've generally been using psql - give it a try and see if it works. Regards, Mike Obe, Regina wrote: > Were you doing it from psql or PgAdmin III? I'm thinking that when you cut > and paste via PgAdmin III that it keeps in whatever linebreaks are in there > and maybe psql strips it? > > Actually its weird because when I did it the first time it worked, but for > some reason consecutive times I couldn't recreate my success (but then I was > directly cutting and pasting from the web page), until I discovered that if I > stripped the line breaks it seemed to work consistently. > > Thanks, > Regina > > -Original Message- > From: Mike Leahy [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 19, 2007 9:43 AM > To: Obe, Regina > Cc: PostGIS Users Discussion; [EMAIL PROTECTED] > Subject: Re: [postgis-users] Combined Windows version of PostGIS/P-LR > > Regina, > > I'm copying this to the plr-general list ([EMAIL PROTECTED]). > > I can't say that I'm getting the same error. I've installed the version > from David's installer, and I did the exact same example from the PL/R > docs that you're trying and it's working for me too. Maybe there's an > encoding problem in your case? > > Mike > > Obe, Regina wrote: >> >> Unfortunately I'm not in the PL/R newsgroup yet and I don't have Joe's email >> address, so can someone pass this message along. I'm updating my >> instruction docs accordingly to reflect this issue and still writing up some >> followup tutorials. >> >> When testing creating the median function as described in the docs using >> PgAdmin, I would get parse R errors when using it. After some research >> (googling - others had the same problem) and refollowing my tracks (since I >> was able to get it right a couple of times, but not consistently). I >> discovered it was the line breaks in the function that was causing the >> problem. >> >> I had to do this >> >> create or replace function r_median(_float8) >> returns float as 'median(arg1)' language 'plr'; >> >> >> instead of what is mentioned in the docs - this >> create or replace function r_median(_float8) returns float as ' >> median(arg1) >> ' language 'plr'; >> >> Evidentally R parser chokes when it runs into line breaks. >> >> Hope that helps, >> Regina >> >> >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TECHER David >> Sent: Monday, June 18, 2007 6:16 PM >> To: [EMAIL PROTECTED]; PostGIS Users Discussion >> Subject: Re: [postgis-users] Combined Windows version of PostGIS/P-LR >> >> Mike Leahy a écrit : >>> Hey all, >>> >>> I'm just wondering if this thread should move back to the plr-general >>> list. In particular, I think it would be good if Joe is able to add a >>> link to the installer on the PL/R web page. I just tried the installer, >>> and it works great - nice work David. >>> >>> I did notice, however, that it claims to be installing PgRouting in the >>> welcome screen, and it appears as PgRouting in the Windows uninstall >>> list. >> Well I did the installer, last Saturday night and I was hurry. >> You know when you haven't slept all a night, you don't see errors >> thanks. >>> Also, I think more than is needed is being added to the Path >>> variable. In my case, this is what gets added after the installer is done: >>> >>> "\;C:\Program Files\R\R-2.5.0\bin;C:\Program >>> Files\PostgreSQL\8.2\bin;C:\Program Files\PostgreSQL\8.2\lib" >>> >>> >> I reduced it to >> >> \;C:\Program Files\R\R-2.5.0\bin >> >>> When I had installed PL/R manually, I seemed to be able to get away with >>> adding just ";C:\Program Files\R\R-2.5.0\bin". >> Yes it is, you're right. But if you use a function like >> >> select load_r_typenames(); >> >> pl/r needs to know what it you R_HOME environment variable. >>> Also, if I happen to >>> re-install PL/R, the entire string above will appear multiple times in >>> the Path variable. Is it possible for the installer to check for that? >>> >>> >> I've only been able to detect if R directory is present in PATH variable >> for the moment! >> Not able to remove it for the moment from a previous install
Re: [postgis-users] Combined Windows version of PostGIS/P-LR
Regina, I'm copying this to the plr-general list ([EMAIL PROTECTED]). I can't say that I'm getting the same error. I've installed the version from David's installer, and I did the exact same example from the PL/R docs that you're trying and it's working for me too. Maybe there's an encoding problem in your case? Mike Obe, Regina wrote: > > Unfortunately I'm not in the PL/R newsgroup yet and I don't have Joe's email > address, so can someone pass this message along. I'm updating my instruction > docs accordingly to reflect this issue and still writing up some followup > tutorials. > > When testing creating the median function as described in the docs using > PgAdmin, I would get parse R errors when using it. After some research > (googling - others had the same problem) and refollowing my tracks (since I > was able to get it right a couple of times, but not consistently). I > discovered it was the line breaks in the function that was causing the > problem. > > I had to do this > > create or replace function r_median(_float8) > returns float as 'median(arg1)' language 'plr'; > > > instead of what is mentioned in the docs - this > create or replace function r_median(_float8) returns float as ' > median(arg1) > ' language 'plr'; > > Evidentally R parser chokes when it runs into line breaks. > > Hope that helps, > Regina > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TECHER David > Sent: Monday, June 18, 2007 6:16 PM > To: [EMAIL PROTECTED]; PostGIS Users Discussion > Subject: Re: [postgis-users] Combined Windows version of PostGIS/P-LR > > Mike Leahy a écrit : >> Hey all, >> >> I'm just wondering if this thread should move back to the plr-general >> list. In particular, I think it would be good if Joe is able to add a >> link to the installer on the PL/R web page. I just tried the installer, >> and it works great - nice work David. >> >> I did notice, however, that it claims to be installing PgRouting in the >> welcome screen, and it appears as PgRouting in the Windows uninstall >> list. > Well I did the installer, last Saturday night and I was hurry. > You know when you haven't slept all a night, you don't see errors > thanks. >> Also, I think more than is needed is being added to the Path >> variable. In my case, this is what gets added after the installer is done: >> >> "\;C:\Program Files\R\R-2.5.0\bin;C:\Program >> Files\PostgreSQL\8.2\bin;C:\Program Files\PostgreSQL\8.2\lib" >> >> > I reduced it to > > \;C:\Program Files\R\R-2.5.0\bin > >> When I had installed PL/R manually, I seemed to be able to get away with >> adding just ";C:\Program Files\R\R-2.5.0\bin". > Yes it is, you're right. But if you use a function like > > select load_r_typenames(); > > pl/r needs to know what it you R_HOME environment variable. >> Also, if I happen to >> re-install PL/R, the entire string above will appear multiple times in >> the Path variable. Is it possible for the installer to check for that? >> >> > I've only been able to detect if R directory is present in PATH variable > for the moment! > Not able to remove it for the moment from a previous install > > If you want, my Inno Setup script is available at > > http://www.davidgis.fr/download/plr-8.2.0.4-win32-src.zip > > If someone is used here to using Inno SetUp, no problem to improve it. > >> Regards, >> Mike >> >> TECHER David wrote: >> >>> Hi Regina, >>> >>> Many thanks for these interesting link! Very excellent! >>> >>> I will wait for the next series of your articles! >>> >>> --david; >>> >>> Obe, Regina a écrit : >>> >>>> Since Dave was doing his part, I figured I would help out the cause a >>>> little. R is pretty new to me too but looks like it could be very useful. >>>> I drafted up a quick getting started and included David's link in there. >>>> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 >>>> >>>> Let me know any parts that seem unclear or where I went wrong. The >>>> steps seemed to work for me. >>>> Thanks, >>>> Regina >>>> >>>> >>>> *From:* [EMAIL PROTECTED] on behalf of >>>> TECHER David >
Re: [postgis-users] Combined Windows version of PostGIS/P-LR
Hey all, I'm just wondering if this thread should move back to the plr-general list. In particular, I think it would be good if Joe is able to add a link to the installer on the PL/R web page. I just tried the installer, and it works great - nice work David. I did notice, however, that it claims to be installing PgRouting in the welcome screen, and it appears as PgRouting in the Windows uninstall list. Also, I think more than is needed is being added to the Path variable. In my case, this is what gets added after the installer is done: "\;C:\Program Files\R\R-2.5.0\bin;C:\Program Files\PostgreSQL\8.2\bin;C:\Program Files\PostgreSQL\8.2\lib" When I had installed PL/R manually, I seemed to be able to get away with adding just ";C:\Program Files\R\R-2.5.0\bin". Also, if I happen to re-install PL/R, the entire string above will appear multiple times in the Path variable. Is it possible for the installer to check for that? Regards, Mike TECHER David wrote: > Hi Regina, > > Many thanks for these interesting link! Very excellent! > > I will wait for the next series of your articles! > > --david; > > Obe, Regina a écrit : >> Since Dave was doing his part, I figured I would help out the cause a >> little. R is pretty new to me too but looks like it could be very useful. >> I drafted up a quick getting started and included David's link in there. >> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 >> >> Let me know any parts that seem unclear or where I went wrong. The >> steps seemed to work for me. >> Thanks, >> Regina >> >> >> *From:* [EMAIL PROTECTED] on behalf of >> TECHER David >> *Sent:* Sun 6/17/2007 2:53 AM >> *To:* PostGIS Users Discussion >> *Subject:* Re: [postgis-users] Combined Windows version of PostGIS/P-LR >> >> Dear Jan, >> >> I begun to use PL/R. As you know, I'm not a expert in R >> >> Compiling with PostgreSQL 8.2.4 on win32 ...no problem! >> >> >> These release is available at >> >> http://www.davidgis.fr/download/plr-8.2.0.4_0_win32.exe >> >> I've tryed to see here if >> >> first R 2.5.0 is available on the computer where you want to install it. >> >> else it stop! >> >> The installer updates the R_HOME environment variable >> and update the PATH in order to adding 'R_HOME'/bin directory >> >> Let me know if it works for you >> >> createdb testR >> >> psql -d testR -f / "path/to/your/postgresql/dir"/share/contrib/plr.sql >> >> psql testR >> >> select load_r_modules(); >> >> select * from plr_environ(); >> >> and so on >> >> --david; >> >> Jan Hartmann a écrit : >> > I agree. A separate installer for PL/R would be the first thing to >> > create. Am I right that that would not be very difficult? After that, >> > though, I would very much like to have a *combined* version, with PL/R >> > and PostGIS both enabled. I am not sure how difficult that would be, >> > but, as I said, it would be a marvelous example of Open Source >> > software cooperation. The additional packages Mike mentioned don't >> > have to be installed prior to installing PL/R; you can install them in >> > the regular way in R (R INSTALL), and an existing PostgreSQL database >> > with PL/R will recognise them after a restart. >> > >> > BTW: deldir was a major reason for me to experimnt with a combined >> > version: the power of voronoi diagrams in a PostGIS database! >> > >> > Jan >> > >> > Mike Leahy wrote: >> >> David/Jan: >> >> >> >> Although PostGIS and PL/R can be complimentary tools, I think a >> separate >> >> installer (for now at least) would make the most sense for PL/R. At >> >> least that way there is still the option for other users to install >> PL/R >> >> without PostGIS, and vice-versa. >> >> >> >> At the same time, however, I can see how a including PL/R as an option >> >> in the PostGIS installer could be useful - particularly if a set of >> >> pre-defined functions were included with the setup that made use of >> PL/R >> >> for spatial statistical operations within PostGIS. But this might >> get a >> >> bit complicated, considering the requirements involved (e.g., having R >> >> installed, as well as packages installed in R - such as deldir for >>
Re: [postgis-users] Combined Windows version of PostGIS/P-LR
David/Jan: Although PostGIS and PL/R can be complimentary tools, I think a separate installer (for now at least) would make the most sense for PL/R. At least that way there is still the option for other users to install PL/R without PostGIS, and vice-versa. At the same time, however, I can see how a including PL/R as an option in the PostGIS installer could be useful - particularly if a set of pre-defined functions were included with the setup that made use of PL/R for spatial statistical operations within PostGIS. But this might get a bit complicated, considering the requirements involved (e.g., having R installed, as well as packages installed in R - such as deldir for making voronoi polygons). Mike TECHER David wrote: > Hi Jan, > > Before I made a mistake, all you want is only a win32-installer for PL/R? > > because I think you know that's postgis win32-installer already exist? > > See http://www.postgis.org/download/windows/ > > --david; > > > Jan Hartmann a écrit : >> Hi folks (sorry for crossposting), >> >> Joe Conway just announced that PL/R will soon be available for >> Windows. See the thread starting at: >> >> http://gborg.postgresql.org/pipermail/plr-general/2007-June/000235.html >> >> PL/R is an implementation of the statistical package R within >> PostgreSQL. See: >> >> http://www.joeconway.com/plr/ >> http://www.r-project.org >> >> It's installed the same way as postGIS, but until now only a Linux >> version was available. I have them both enabled on my server at SARA >> computing center, and the functionality is unbelievable. My own >> organisation, however, doesn't allow me to install Linux servers, and >> even if they did, neither staff nor students would be willing or even >> able to work with them. I guess this is not a unique situation, so >> this Windows port is really important. Moreover, a PostgreSQl >> implementation with full GIS and statistical functionality is >> something that doesn't exist in the commercial world as far as I know. >> An easy installable Windows version would IMO really be a showpiece >> for Open Source software. I would really appreciate if people in the >> know on both these lists would put their heads together to implement >> this. I don't really know much about Windows installers, but my >> feeling is that it is not enormously difficult. >> >> Regards, >> >> Jan Hartmann >> >> Dr. J. Hartmann >> Department of Geography >> University of Amsterdam >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > > > > > > > ___ > Yahoo! Mail r�invente le mail ! D�couvrez le nouveau Yahoo! Mail et son > interface r�volutionnaire. > http://fr.mail.yahoo.com > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
