[postgis-users] OT: Question on performance in plpgsql function

[Chris Hermansen]

Stephen, this is just a wild guess and going from memory to boot...

I believe there can be an issue with pgsql where the reader and writer
processes interfere with each other and the writer "stalls".

There are ways to detect this if it is happening and configuration
parameters to set that are meant to help.

It seems to me we have run across this in our shop when we have update
processes running through an entire long (and maybe wide) table.

Perhaps this is at the root of your problem.
On Sep 6, 2011 6:39 AM, "Stephen Woodbridge" <woodbri at swoodbridge.com>
wrote:
> 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 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110906/ec165c9e/attachment.html>