[Pgpool-general] Bad config? Bug? Or something else? And a question on logging.

[salman]

Please see below for config.

We have recently switched to using pgpool-2 for connection pooling here 
and have come across a strange issue; this was done mainly to make it 
easier to point our apps at the master or slave db server when doing 
maintenance on either machine (both machines are sync data via slony). 
Please note that we're not using pgpool for parallel querying, or 
replication.

In the past few weeks, I've noticed that quite randomly, some of our web 
scripts have been leaving 'idle in transaction' (IIT) queries open on 
the db server which 1) causes slony to react very angrily, and 2) causes 
500 errors to come back for any subsequent page requests by 
clients/staff (not site-wide, only for the data generated by the scripts 
in question).

One script in particular runs a query which can take 90-120 seconds to 
complete and is run through our CherryPy service -- no matter what the 
load on the db machine is, this script seems to leave timed-out, IIT 
queries open.

Restarting CherryPy does not get rid of them. The only way to clean 
these open connections seems to be to restart pgpool or kill the pgpool 
child that's hung.

I am trying to eliminate components which may be causing the problem and 
would like opinions on whether there's something I can change in my 
config which may be what is the root of the problem.

Lastly, is there a way to have pgpool dump out statements sent to it 
w/out enabling debug logging? Dumping full debug data generates very 
large log files which causes the system to run out of space rather 
quickly on /var.


If you require any other info, please let me know.


Config starts here:
-----------------------------------------------------------
listen_addresses = '*'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 250
max_pool = 1
child_life_time = 300
connection_life_time = 300
child_max_connections = 100
client_idle_limit = 0
authentication_timeout = 60
logdir = '/var/run/pgpool'
replication_mode = false
replication_timeout = 5000
load_balance_mode = false
replication_stop_on_mismatch = false
replicate_select = false
reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
print_timestamp = true
master_slave_mode = false
connection_cache = true
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
insert_lock = false
ignore_leading_white_space = true
log_statement = true
log_connections = true
log_hostname = true
parallel_mode = false
enable_query_cache = false
pgpool2_hostname = ''
#db03
backend_hostname0 = 'x.x.x.x'
#db01
#backend_hostname0 = 'x.x.x.x'
backend_port0 = 5432
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
-----------------------------------------------------------