[pgpool-general: 673] Re: Transaction never finishes

Tatsuo Ishii ishii at postgresql.org
Fri Jun 29 08:06:33 JST 2012

> Hello,
> I'm using pgpool-II 3.1.2 with streaming replication and it's working
> pretty well. But I'm dealing with a weird situation and I don't know
> how to debug:
> Sometimes, some transactions never finishes in the master, here is an
> example, the following query:
> select * from pg_stat_activity where xact_start < current_timestamp -
> '10 minutes'::interval
> Results:
> 20994;"****";2445;16385;"****";"";"192.168.**.**";"";44083;"2012-06-27
> 05:55:39.525881-03";"2012-06-27 11:17:46.475347-03";"2012-06-27
> 11:18:10.044718-03";f;"<IDLE> in transaction"
> This transaction gets AccessShareLock in the relations:
> pg_class_relname_nsp_index
> pg_class
> pg_class_oid_index
> pg_namespace
> pg_namespace_oid_index
> pg_namespace_nspname_index
> And one ExclusiveLock that I couldn't identify the relation.
> Sometimes, depending on the relations that are locked everything fails
> (timeout) and a pgpool restart must be done. Anyone knows what is
> going on?

You want to identify the process id of pgpool which is dealing with
PostgreSQL backend process id(in the case above it's 2445).

Here is the step to find pgpool process id:

1) Execute pcp_proc_count to find pgpool process list. This command
   returns all pgpool process ids.

2) For each process id in #1, execute pcp_proc_info. This will tell
   what are the PostgreSQL process ids. Note that the command returns
   multiple rows sorting by node id. Usually node id 0 (thus the first
   line) is for primary.

3) Look for 2445 in #2 to find the pgpool process id.

4) If you could find the pgpool process id (say 12345), grep pgpool
   log by using 12345. This will show what's going on with 12345.
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

More information about the pgpool-general mailing list