Обсуждение: Some problem with the NOTIFY/LISTEN
HI
I am using PG 8.1.4, and my server was stop service, and when I checked the log, I found that some process had locked pg_listener in ExclusiveLock, it blocked all of the following processes which use NOTIFY/LISTEN. Can anyone tell me why pg_listener is locked forever?
max | granted | locks | pid | query_age | current_query
-------------------------------+---------+-------+-------+-----------------+-----------------
2011-01-09 08:12:01.941403+08 | t | 1 | 23864 | 00:00:13.863773 | LISTEN LABC20
2011-01-09 08:12:01.941403+08 | t | 1 | 20223 | 05:18:36.472416 | <IDLE>
2011-01-09 08:12:01.941403+08 | t | 1 | 23453 | 00:00:43.793547 | LISTEN LABD21
2011-01-09 08:12:01.941403+08 | t | 1 | 20051 | 00:01:30.370326 | <IDLE>
.....
2011-01-09 08:12:01.941403+08 | t | 1 | 19805 | 00:01:23.648079 | <IDLE>
2011-01-09 08:12:01.941403+08 | t | 1 | 23872 | 00:00:13.79683 | LISTEN LABD21
2011-01-09 08:12:01.941403+08 | t | 1 | 23844 | 00:00:15.338939 | LISTEN LABE11
(100 rows)
now | relname | transaction | pid | mode | granted
-------------------------------+-------------+-------------+-------+-----------------+---------
2011-01-09 08:11:01.883414+08 | pg_listener | 835435890 | 19759 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835436051 | 20444 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835435898 | 19695 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835435963 | 20090 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835436024 | 20285 | ExclusiveLock | f
......
2011-01-09 08:11:01.883414+08 | pg_listener | 835436007 | 20247 | ExclusiveLock | f
(176 rows)
Before that there are two deadlock detected
Jan 9 08:10:40 SUC01 postgres[19767]: [2-1] ERROR: deadlock detected
Jan 9 08:10:40 SUC01 postgres[19767]: [2-2] DETAIL:
Jan 9 08:12:32 SUC01 postgres[19767]: [3-1] ERROR: deadlock detected
Jan 9 08:12:32 SUC01 postgres[19767]: [3-2] DETAIL:
Jan 9 08:12:32 SUC01 postgres[19767]: [3-3] STATEMENT: UNLISTEN LABD11
Thanks a lot
Richard
I am using PG 8.1.4, and my server was stop service, and when I checked the log, I found that some process had locked pg_listener in ExclusiveLock, it blocked all of the following processes which use NOTIFY/LISTEN. Can anyone tell me why pg_listener is locked forever?
max | granted | locks | pid | query_age | current_query
-------------------------------+---------+-------+-------+-----------------+-----------------
2011-01-09 08:12:01.941403+08 | t | 1 | 23864 | 00:00:13.863773 | LISTEN LABC20
2011-01-09 08:12:01.941403+08 | t | 1 | 20223 | 05:18:36.472416 | <IDLE>
2011-01-09 08:12:01.941403+08 | t | 1 | 23453 | 00:00:43.793547 | LISTEN LABD21
2011-01-09 08:12:01.941403+08 | t | 1 | 20051 | 00:01:30.370326 | <IDLE>
.....
2011-01-09 08:12:01.941403+08 | t | 1 | 19805 | 00:01:23.648079 | <IDLE>
2011-01-09 08:12:01.941403+08 | t | 1 | 23872 | 00:00:13.79683 | LISTEN LABD21
2011-01-09 08:12:01.941403+08 | t | 1 | 23844 | 00:00:15.338939 | LISTEN LABE11
(100 rows)
now | relname | transaction | pid | mode | granted
-------------------------------+-------------+-------------+-------+-----------------+---------
2011-01-09 08:11:01.883414+08 | pg_listener | 835435890 | 19759 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835436051 | 20444 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835435898 | 19695 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835435963 | 20090 | ExclusiveLock | f
2011-01-09 08:11:01.883414+08 | pg_listener | 835436024 | 20285 | ExclusiveLock | f
......
2011-01-09 08:11:01.883414+08 | pg_listener | 835436007 | 20247 | ExclusiveLock | f
(176 rows)
Before that there are two deadlock detected
Jan 9 08:10:40 SUC01 postgres[19767]: [2-1] ERROR: deadlock detected
Jan 9 08:10:40 SUC01 postgres[19767]: [2-2] DETAIL:
Jan 9 08:12:32 SUC01 postgres[19767]: [3-1] ERROR: deadlock detected
Jan 9 08:12:32 SUC01 postgres[19767]: [3-2] DETAIL:
Jan 9 08:12:32 SUC01 postgres[19767]: [3-3] STATEMENT: UNLISTEN LABD11
Thanks a lot
Richard
lee Richard <clipper.kenyon@gmail.com> writes: > I am using PG 8.1.4, and my server was stop service, and when I checked the > log, I found that some process had locked pg_listener in ExclusiveLock, it > blocked all of the following processes which use NOTIFY/LISTEN. Can anyone > tell me why pg_listener is locked forever? NOTIFY and LISTEN do require exclusive locks in PG 8.1, so this report isn't exactly surprising. Most likely you had some open transaction that had done one or the other and then gone to sleep. There is a significant bug fix in 8.1.12 that might or might not have changed the result for you: http://archives.postgresql.org/pgsql-patches/2008-03/msg00181.php A more radical answer would be to upgrade to 9.0, which has a totally different and much better-performing implementation of LISTEN/NOTIFY. In any case keep in mind that 8.1.x is now considered EOL and unsupported. Whether or not you're prepared to make the jump to 9.0 now, you need to think about getting off 8.1. And *especially* about getting off a four-year-old minor release of 8.1. regards, tom lane