Обсуждение: 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

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