[Pgpool-general] insert_lock leaks

[Simone Tregnago]

Yoshiyuki Asaba wrote:
  > 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?

Hi,
I can tell you that it's possible that some connection remains opened, 
but no one execute explicit locks. Maybe I don't know PostgreSQL but I 
don't understand how an idle connection can keep a lock without using an 
explicit 'lock' statement.
Second: without pgpool lock option, this situation never occours. So, if 
it's a client leak, I cannot understand why this problem don't raise 
without pgpool locks.
Third: for now this deadlock is always happened with the same table 
(presenze.bolday). This is one of the rare tables that use serial 
fields. If this helps...
Note that I'm using PostgreSQL 8.1, could this mean some deadlock 
trouble that PostgreSQL 8.3 doesn't suffer?

Thanks,
Simone Tregnago