Re: how to release a transaction lock on a table?

Hi.  Sorry about the confusion in terminology.

You are right.  The transactions are idle--when I do a "ps auxw" on my 
database server, I see "....idle in transaction".  Is this what you 
meant, and would the steps you talked about with pg_stat_activity help 
me track down the transactions that are idle?

What's strange is that usually this does not cause problems.  It is just 
occasionally that I am unable to gain access to a table.  Unfortunately 
that also makes it hard to track down the source of the problem.

Thanks for all your help so far--really appreciate it.

Si

Michael Fuhr wrote:

>On Tue, Feb 01, 2005 at 10:53:11AM -0800, Si Chen wrote:
>
>  
>
>>I would like to track down what in the application is causing the 
>>deadlock,
>>    
>>
>
>Are you sure you understand what "deadlock" means?  Deadlock occurs,
>for example, when connection A holds a lock that connection B wants
>and connection B holds a lock that connection A wants.  PostgreSQL
>should recognize that situation and cause one of the connections
>to fail after a timeout (one second by default).  That doesn't sound
>like what you're experiencing -- based on what you've said, one
>connection holds a lock and another is blocked waiting for it.
>
>  
>
>>but it's a bit hard since it's a big app with lots going on.  
>>I can track down the PID of the transaction which is locking the tables, 
>>but is there anyway to go from the PID back to the SQL statement(s) in 
>>the transaction?
>>    
>>
>
>The query "SELECT * FROM pg_stat_activity" should show connections'
>current queries if you have stats_command_string set to "on".  If
>stats_command_string is "off" then you can enable it by editing
>postgresql.conf and restarting the postmaster, but unfortunately
>that won't help you track down queries that are already running.
>
>Is it possible that the transaction holding the lock is idle?  Some
>applications use long-lived transactions that can cause locking
>problems in other transactions.
>
>  
>