[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