Re: simple update query stuck

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen
Sent: Tuesday, April 01, 2014 3:51 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] simple update query stuck

Hello,

I'm using postgresql 9.0.13, and I have a simple query that seems to be stuck.  I did a
postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity;

 procpid |          query_start          | waiting |                                                                  
                                                 current_query                                                        
                       
                                     
   32605 | 2014-04-01 12:39:46.957666-07 | t       | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1,
FROM_DATE=$2,THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7,
CREATED_TX_STAMP=$8WHERE GL_ACCOUNT 
_ID=$9 AND ORGANIZATION_PARTY_ID=$10

   32685 | 2014-04-01 12:25:10.378481-07 | t       | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1,
FROM_DATE=$2,THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7,
CREATED_TX_STAMP=$8WHERE GL_ACCOUNT 
_ID=$9 AND ORGANIZATION_PARTY_ID=$10

As you can see this is a pretty simple update query, but it's been running 24 minutes in one thread and nearly 10
minutesin the other thread.  What's also strange is it's not trigger a transaction timeout either. 

I've already re-started the database recently, and there's not too many threads executing -- just 38 threads total.  Is
itpossible that the table is corrupted or needs repair? 

--
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps

Both queries are "waiting".  Your table must be locked.
Check pg_locks.

Regards,
Igor Neyman