Dear Pavel, Thanks for quick response.
Yes I am using explicit locks but only at row-level, not at table level, will this cause sequential execution? select ...on primary key..... for update; My objective is to execute this function in parallel for different PK entities, for same primary key value, sequential is fine. Also I have noted that though the function execution after the code calls is taking more than 2-3 minutes but , when I have put in notice statements for start/end of function it has only taken a second in pg_log. Code is definitely not the problem here, as earlier function logic was getting executed as multiple queries from the code and were working fine for even higher load before I moved to function for the same. This problem started after moving to function only. I doubt that query is the reason for slowness, and it seems to be more related to database load at times, because it happens 3-4 times out of every 4000 transactions. pg_stat doesn't show any locks at the moment, but might not reflect the actual scenario as currently the transactions are working fine. Regards, Vishal Gupta - 9910991635 ----- Original Message ----- From: "Pavel Stehule" <pavel.steh...@gmail.com> To: "Vishal Gupta" <visha...@pinelabs.com> Cc: pgsql-performance@postgresql.org Sent: Wednesday, December 16, 2009 1:04:48 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi Subject: Re: [PERFORM] Parallel Function calls using multiple processes 2009/12/16 Vishal Gupta <visha...@pinelabs.com>: > Problem: Function call typically takes 2-3 millisecond but at times > exceeding 2-3 Minutes. > ===================================================== > From the DB logs it seems when multiple processes are trying to execute the > function, > execution takes sequentially rather than parallel, which means Nth thread > will have to wait for (N-1)*ExecutionTime before getting its turn it's depend - if there are some locks then yes. but reason could be a slow query inside procedure too - look on pg_stat_activity table, if there are processes waiting for lock. see http://old.nabble.com/Query-is-slow-when-executing-in-procedure-td26490782.html Regards Pavel Stehule > > Is my observation correct? If yes then what is the solution for this? If not > where/how to find the exact cause of the above problem? > ===================================================== > DB Version: 8.2 > Function Details: > --returns numeric, takes 10 parameters > --select query to validate data > --row level lock for select and validate > --bare minimum business logic > --update data > --couple of inserts for transaction logs/account management > --also note that few of the tables have audit triggers causing the row to be > inserted in audit table with the action (only Update/Insert/Delete) > >
