Re: Performance of the listen command

A long time ago, in a galaxy far, far away, ff@partyticket.net (Flemming Frandsen) wrote:
> I just looked at the pg_listener table:
>
> zepong-> \d+  pg_listener
>           Table "pg_catalog.pg_listener"
>     Column    |  Type   | Modifiers | Description
> --------------+---------+-----------+-------------
>  relname      | name    | not null  |
>  listenerpid  | integer | not null  |
>  notification | integer | not null  |
> Has OIDs: no
>
>
> ... and noticed the complete lack of indexen, surely this must be a bug?

There has been intent to change pg_listener to be an in-memory
structure rather than a table; that would have two benefits:

a) No more disk access...

b) Listen values all forcibly become obsolete any time the postmaster
restarts; an in-memory structure would properly disappear at  those
time rather than persisting.

There's a demerit:

c) If there are a LOT of events, that might not fit in memory nicely.

> When trying to create the index I get told off by pg:
>
> create unique index pg_listeners on pg_listener (relname, listenerpid);
> ERROR:  permission denied: "pg_listener" is a system catalog

No point in adding an index if this is to be replaced.

That being said, if it's not going away just yet, it might be a good
addition...

You can't add the index; altering system tables isn't permissible...

> Any ideas, other than run VACUUM pg_listener every 10 minutes?

Remarkably enough, that is exactly the solution that the Slony-I,
which makes use of LISTEN/NOTIFY, uses.  The Slony-I cleanup thread
vacuums pg_listener (and some other tables) every 10 minutes.
--
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxfinances.info/info/emacs.html
Instead of  talking to your plants,  if you yelled at  them would they
still grow, only to be troubled and insecure?