Re: LISTEN considered dangerous

On Wed, 2 Aug 2006, Roman Neuhauser wrote:

> > I'm bothered by listen listening from the end of the transaction in
> > stead of the start of the transaction.
>
> Sorry if this isn't what you're after, instead just a question:
>
> Why don't you issue the LISTEN in a separate transaction before
> going on?
>
> LISTEN foo;
> BEGIN;
> SELECT ...

Well that's exactly what I do, but in stead of doing LISTEN foo I do
155x LISTEN foo before committing.

It's not too bad as it only has to happen once pr. connection, but it
creates a huge amount of churn in the pg_listeners table and there is an
extra delay, simply because of the 155 roundtrips and the extra commit.

I think I might put the listen stuff in a stored procedure to cut down on
the roundtrips, but it's still not ideal.

Many of my connections don't need the full 155 listens, so what I'd really
like to be able to do is:

select * from foo;
listen foochanges;

and actually get events if foo is updated compared to the state that
the select read.