
I was found a bug in PostgreSQL 8.0.0beta3.
It isn't in PostgreSQL 7.4.5.

$ createdb test
$ psql test

test=# SELECT version ();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4
(1 row)

test=# LISTEN a;
test=# LISTEN b;

test=# SELECT * FROM pg_listener l WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=l.listenerpid);
 relname | listenerpid | notification
---------+-------------+--------------
 b       |        2020 |            0
(1 row)

test=# LISTEN c;
test=# LISTEN d;
test=# LISTEN e;
test=# LISTEN f;

test=# SELECT * FROM pg_listener l WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=l.listenerpid);
 relname | listenerpid | notification
---------+-------------+--------------
 b       |        2020 |            0
 d       |        2020 |            0
 f       |        2020 |            0
(3 rows)

test=# SELECT * INTO temp_listener FROM pg_listener;
SELECT
test=# SELECT * FROM temp_listener l WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=l.listenerpid);
 relname | listenerpid | notification
---------+-------------+--------------
 b       |        2020 |            0
 d       |        2020 |            0
 f       |        2020 |            0
(3 rows)

(not a pg_listener bug)

test=# SELECT * INTO temp_stat_activity FROM pg_stat_activity;
SELECT
test=# SELECT * FROM temp_listener l WHERE NOT EXISTS (select 1 from temp_stat_activity a where a.procpid=l.listenerpid);
 relname | listenerpid | notification
---------+-------------+--------------
(0 rows)

(not an EXISTS bug)


Note: If backend terminated abnormally or server powered down, the
pg_listener's records cannot be deleted.

I using a script at times:

DELETE FROM pg_listener WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=pg_listener.listenerpid);

In PostgreSQL 7.4.5 it's working fine.

"TODO: Allow LISTEN/NOTIFY to store info in memory rather than tables" is
good idea. How soon may i expect this feature? :)




								-Sygma

Tue, 28 Sep 2004 10:22:05 +0200 (CEST)
=?ISO-8859-1?Q?Szima_G=E1bor?= <sygma@axelero.hu>
pg_stat_activity EXISTS bug in 8.0.0beta3
=?ISO-8859-1?Q?Szima_G=E1bor?= <sygma@axelero.hu> writes:
> I was found a bug in PostgreSQL 8.0.0beta3.

Fixed, thanks.

> It isn't in PostgreSQL 7.4.5.

Actually it is...

			regards, tom lane

Fri, 01 Oct 2004 17:04:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_stat_activity EXISTS bug in 8.0.0beta3 