[Pgpool-general] insert_lock leaks

[Yoshiyuki Asaba]

Hi,

From: Simone Tregnago <simonetregnago at grivaonline.com>
Subject: Re: [Pgpool-general] insert_lock leaks
Date: Wed, 04 Jun 2008 10:59:44 +0200

> It's happened again. I've encountered a lock with a delete query.
> I attach all the info you've requested.
> As you can see in the stats of the 1st PostgreSQL server the query:
> "delete from presenze.bolday where "BADGECODE"=...."
> is blocked by this lock:
> "LOCK TABLE "presenze"."bolday" IN SHARE ROW EXCLUSIVE"

Thanks. I read the logs. The problem was caused by the following
processes on pg_server1.

  pid=19450) LOCK TABLE bolday(OID=74534964)

  pid=19452) DELETE FROM bolday

  pid=19454) idle in transaction

pg_locks:
  relation      | ... | 19450 | ShareRowExclusiveLock | f
  relation      | ... | 19452 | RowExclusiveLock      | f
  relation      | ... | 19454 | RowExclusiveLock      | t

pg_stat_activity:
  19450 | LOCK TABLE "presenze"."bolday" ...|
  19452 | delete from presenze.bolday ... |
  19454 | <IDLE> in transaction |

19454 held the table lock with RowExclusive mode and did not close
a transaction yet.

Was there a process that did not close a transaction and keep idle?

Regards,
--
Yoshiyuki Asaba
y-asaba at sraoss.co.jp

> 
> hoping that this could mean something,
> thanks
> 
> Simone Tregnago
> 
> 
> Yoshiyuki Asaba wrote:
> > Hi,
> > 
> > From: Simone Tregnago <simonetregnago at grivaonline.com>
> > Subject: Re: [Pgpool-general] insert_lock leaks
> > Date: Fri, 30 May 2008 09:48:11 +0200
> > 
> > 
> > Thanks. I wrote a test program and ran it. However I could not
> > reproduce. Could you collect the following outputs when the problem
> > reproduces?
> > 
> > On pgpool Server:
> > 
> >   % ps auwwx | grep pgpool
> > 
> > On PostgreSQL Servers:
> > 
> >   % ps auwwx | grep postgres
> >   % psql -c 'select * from pg_locks' <your DB>
> >   % psql -c 'select * from pg_stat_activity' <your DB>
> > 
> > Regards,
> > --
> > Yoshiyuki Asaba
> > y-asaba at sraoss.co.jp
>