From: | Szima Gábor <sygma(at)axelero(dot)hu> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | pg_stat_activity EXISTS bug in 8.0.0beta3 |
Date: | 2004-09-28 08:22:05 |
Message-ID: | Pine.LNX.4.50.0409280944020.906-100000@vigo.sygma.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Sigaev | 2004-09-28 10:59:47 | Optimizer and function returning setof int4 |
Previous Message | Simon Riggs | 2004-09-28 07:26:00 | Re: Reviving Time Travel (was Re: 'TID index') |