Do these queries update more than one row? I ran into a similar issue a year ago, where two multi-row updates would deadlock because they processed rows in a different order. I'd love to see UPDATE support ORDER BY to fix this, but it doesn't yet. (If I ever try contributing to Postgres, this is a feature I'd love to add. It seems like it might be an easy one for a newbie to tackle.)
Paul On Tue, Apr 1, 2014 at 12:51 PM, Si Chen <sic...@opensourcestrategies.com> wrote: > 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=$8 WHERE > 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=$8 WHERE > 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 minutes in 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 it possible that the table > is corrupted or needs repair? > > -- > Si Chen > Open Source Strategies, Inc. > sic...@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > -- _________________________________ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
