On 9/5/2011 11:26 PM, Stephen Woodbridge wrote:
Hi all,
This may be a little off topic, but there are a lot of smart minds here
that might know.
I have a plpgsql function the does some work to compute all the
intersections with street names in a road network. It creates a vertex
table like in pgrouting and an intersection table. Then populates the
tables from a streets network and finally updates the intersection table
based on the number of connected segments that have names.
The performance issue I'm having is that if I include the final update
in the procedure is takes forever (well longer than 12 hours when I
aborted it) and postgres runs at 100 CPU for all that time. But if I
comment out the update and run the procedure it takes say 1 hour to run,
and then I then run the update from the command line it takes say
another hour to run that, so a total of 2 hours.
OK, so here are some real numbers as i just aborted it after 48000 sec
and rerunning part one without the update took 460 sec. and then running
the update alone took 75 sec. So aborted at about 13 hrs vs completing
in two steps in 9 minutes.
And after reading the docs some more I can not do a commit in any stored
procedure, I guess I will have to wrap the two commands into a
client-side script, unless anyone has any other ideas on why this is
happening.
-Steve
plpgsql function run in a single transaction and breaking it into two
steps does it in two transaction, so I figure that has something to do
with it. An you can not COMMIT in a plpgsql function.
Any thoughts on how to fix this?
Can a SQL procedure do a COMMIT? If so I might be able to reorg the
process into two some plpgsql procedures that get called from a SQL
procedure.
Thoughts?
-Steve
_______________________________________________
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
