Search Postgresql Archives

Re: simple update query stuck

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux