Re: Performance of the listen command

A long time ago, in a galaxy far, far away, ff(at)partyticket(dot)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?