The following bug has been logged online:

Bug reference: 1259
Logged by: Vadim Passynkov

Email address: Vadim.Passynkov (AT) pathcom (DOT) com

PostgreSQL version: 7.4.5

Operating system: FreeBSD 4.10-STABLE

Description: garbage in pg_listener after backend crash

Details:

If current backend crash after registers on the notification condition
<name> ( 'LISTEN <name>' SQL command ) row in the pg_listener still exists;

================================================== ====
template1=# SELECT pg_backend_pid ();
pg_backend_pid
----------------
6312
(1 row)

template1=# LISTEN test;
LISTEN

template1=# SELECT * from pg_listener where listenerpid = 6312;
relname | listenerpid | notification
---------+-------------+--------------
test | 6312 | 0
(1 row)

template1=# SELECT crash_backend ( );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

!> \c template1 pgsql
You are now connected to database "template1" as user "pgsql".
template1=# SELECT * from pg_listener where listenerpid = 6312;
relname | listenerpid | notification
---------+-------------+--------------
test | 6312 | 0
(1 row)

================================================== ====

/* crash_backend.c */
#include "postgres.h"
#include "executor/spi.h"

PG_FUNCTION_INFO_V1 ( crash_backend );
Datum crash_backend ( PG_FUNCTION_ARGS ) {
char *ptr = NULL;

*ptr = '1';
PG_RETURN_INT32 ( 1 );
}

================================================== ====

cc -Wall -fpic -I/usr/local/include/postgresql/server -I/usr/local/include
-c crash_backend.c -o crash_backend.o
cc -shared -o crash_backend.so crash_backend.o

================================================== ====

CREATE OR REPLACE FUNCTION crash_backend ( ) RETURNS integer AS
'/usr/home/pvi/pg_bug/crash_backend.so', 'crash_backend' LANGUAGE 'C';

================================================== ====

--
Vadim Passynkov


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
This is not a bug, it's the intended recovery path. The row will go
away as soon as some other backend tries to signal it and finds the
PID no longer exists.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match