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
<sichen@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.
> sichen@opensourcestrategies.com
> http://www.OpenSourceStrategies.com
> LinkedIn: http://www.linkedin.com/in/opentaps
> Twitter: http://twitter.com/opentaps
>
--
_________________________________
Pulchritudo splendor veritatis.
