Обсуждение: System hangs when Insert/Update

Hello,
  I am using PostgreSQL 7.0.2 on RH 6.2

  The DB works smoothly in the past 8 months but until recently, the
system would hang when doing insert or update statements.

  When I use
    ps aux
  I found a lot of processes like
    postgres localhost xxx xxx INSERT
    postgres localhost xxx xxx SELECT
    postgres localhost xxx xxx idle
  are running

  When I killed one process using
    kill -TERM pid
  all those processes gone and the DB returns to normal. But after some
insert/update, the problem exists again...

  I tried to use strace on the process and found that they seems waiting

for something since the following statements are execution repeatedly
    select (0, NULL, NULL, .....
  Maybe they had been fall in some infinite loop..

  Could you give me some help ?

  Best regards,
--
Lee Kwok Shing (***)
http://www.geocities.com/lkslee.geo

Lee Kwok Shing <kslee@graduate.hku.hk> writes:
> for something since the following statements are execution repeatedly
>     select (0, NULL, NULL, .....

Sounds like the backends are waiting for a spinlock.  Could you
provide a debugger stack trace?

            regards, tom lane

Lee Kwok Shing <kslee@graduate.hku.hk> writes:
>   I am using PostgreSQL 7.0.2 on RH 6.2
>   The DB works smoothly in the past 8 months but until recently, the
> system would hang when doing insert or update statements.

Second thought on this: do you have any clients that might disconnect in
the middle of a transaction?  If so, update to 7.0.3 (or later).

            regards, tom lane

Hi Tom,
  Thanks for yout information.

  There should be no clients disconnect in the middle of a transaction since
the problem happens even if there is only one update.

More have capture some traces, the query seems hanged after the phase
  ProcessQuery
and before
  CommitTransactionCommand

  The query plan and the parser outputs process phase has been passed.

  So it seems that the query is stuck at committing the transaction...

  It is even strange that after I killing the process and issue the same
query again, it success !!!

  Thanks again !
  Regards,

Lee Kwok Shing

Tom Lane wrote:

> Lee Kwok Shing <kslee@graduate.hku.hk> writes:
> >   I am using PostgreSQL 7.0.2 on RH 6.2
> >   The DB works smoothly in the past 8 months but until recently, the
> > system would hang when doing insert or update statements.
>
> Second thought on this: do you have any clients that might disconnect in
> the middle of a transaction?  If so, update to 7.0.3 (or later).
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Lee Kwok Shing (***)
http://www.geocities.com/lkslee.geo