Hi,
PostgreSQL 9.2.2, Ubuntu 11.10, Linux 3.0.0.
A couple of days ago I noticed a strange output from a cron command I
use to terminate long transactions.
psql --no-psqlrc --single-transaction -d
postgres -t -c "SELECT
pg_terminate_backend(pid),now(),now()-xact_start as duration,* from
pg_stat_activity where (now() - pg_stat_activity.xact_start) > '60
min'::interval and usename NOT IN ('postgres', 'slony', 'backuper')" |
grep -v '^$'
t | 2013-04-22 17:50:01.452166+04 |
01:00:41.024359 | 16402 | sports | 21945 | 57857517 |
push_io_notifications.app | | 127.0.0.1 |
| 44784 | 2013-04-22 16:49:20.417845+04 | 2013-04-22
16:49:20.427807+04 | 2013-04-22 16:49:20.427807+04 | 2013-04-22
16:49:20.427838+04 | f | idle | LISTEN fb_marker_insert;
Everything is fine here except the status of the process. It is "idle"
despite xact_start was not null. I expected it should always be "idle
in transaction" in such cases.
Are there any exceptions from this rule? May be something connected with LISTEN?
--
Kind regards,
Sergey Konoplev
Database and Software Consultant
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
