Re: simple update query stuck

You are right. That was the problem. I tried the query from
http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT
transaction that was blocking it.

I restarted postgresql again, and (it seems) everything went back to
normal. Was there another way to unlock the table then?

On Tue, Apr 1, 2014 at 1:10 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

>
>
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Si Chen
> Sent: Tuesday, April 01, 2014 3:51 PM
> To: pgsql-general(at)postgresql(dot)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=$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(at)opensourcestrategies(dot)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
>

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