Hi all, I have some idle transactions in PostgreSQL 9.2.4 server which never end. My application was working fine on version 9.1 (BSD) but the problem appeared immediately as we ported it to 9.2.4 on Linux. The idle operations would frequently appear as COMMITs but sometimes I observed INSERTS as well. Please note that each commit below refers to *very light* inserts of single rows which are supposed to last milliseconds. However some of the just never end even if they are not waiting on other operations. See below:
SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity; datname | usename | pid | client_addr | waiting | query_start | query ----------+-----------+-------+-------------+---------+------------------------------- AppQoSDB | appqosusr | 17015 | 127.0.0.1 | f | 2013-10-15 10:21:38.501435+01 | select AppQoSConfig.get_ip_currentnode() AppQoSDB | appqosusr | 17016 | 127.0.0.1 | f | 2013-10-15 10:21:38.502346+01 | COMMIT AppQoSDB | appqosusr | 17017 | 127.0.0.1 | f | 2013-10-15 10:21:38.584794+01 | select AppQoSConfig.get_ip_currentnode() AppQoSDB | appqosusr | 17018 | 127.0.0.1 | f | 2013-10-15 10:21:38.586073+01 | COMMIT AppQoSDB | appqosusr | 17019 | 127.0.0.1 | f | 2013-10-15 09:20:32.724517+01 | select AppQoSConfig.get_ip_currentnode() AppQoSDB | appqosusr | 17021 | 127.0.0.1 | f | 2013-10-15 10:21:38.622651+01 | select AppQoSConfig.get_ip_currentnode() AppQoSDB | appqosusr | 17020 | 127.0.0.1 | f | 2013-10-15 09:20:32.724433+01 | select AppQoSConfig.get_ip_currentnode() AppQoSDB | appqosusr | 17022 | 127.0.0.1 | f | 2013-10-15 10:21:37.368831+01 | select AppQoSConfig.get_ip_currentnode() AppQoSDB | appqosusr | 17024 | 127.0.0.1 | f | 2013-10-15 09:20:32.828307+01 | COMMIT AppQoSDB | appqosusr | 17026 | 127.0.0.1 | f | 2013-10-15 10:21:38.624378+01 | COMMIT AppQoSDB | appqosusr | 17023 | 127.0.0.1 | f | 2013-10-15 09:20:32.828302+01 | COMMIT AppQoSDB | appqosusr | 17025 | 127.0.0.1 | f | 2013-10-15 10:21:37.369869+01 | COMMIT AppQoSDB | appqosusr | 17027 | 127.0.0.1 | f | 2013-10-15 10:21:38.633244+01 | SELECT datname,usename,pid,client_addr,waiting,query_start,query FROM pg_stat_activity; This is the current configuration: name | current_setting ------------------------------+------------------------------------------------------------------------------------------------------------ version | PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1) 4.7.2, 64-bit autovacuum | on autovacuum_analyze_threshold | 500000 autovacuum_max_workers | 1 autovacuum_naptime | 1h autovacuum_vacuum_threshold | 500000 checkpoint_completion_target | 0.7 checkpoint_segments | 128 checkpoint_warning | 30s client_encoding | UTF8 effective_cache_size | 16GB lc_collate | en_GB.UTF-8 lc_ctype | en_GB.UTF-8 listen_addresses | * log_destination | syslog, stderr log_min_duration_statement | 2min log_rotation_age | 10d log_rotation_size | 100MB logging_collector | on max_connections | 200 max_stack_depth | 2MB server_encoding | UTF8 shared_buffers | 6GB synchronous_commit | off TimeZone | GB wal_buffers | 128kB work_mem | 18MB Thank you, Svetlin Manavski