Re: LISTEN considered dangerous

On Thu, Aug 03, 2006 at 12:43:47AM +0200, Flemming Frandsen wrote:
> On Wed, 2 Aug 2006, Tom Lane wrote:
>
> > Flemming Frandsen <ff(at)partyticket(dot)net> writes:
> > > The listen should simply listen for events issued at the start of the
> > > transaction it's executed in.
> >
> > BEGIN;
> > SELECT sleep(1000000000);
> > LISTEN foo;
> >
> > No, I don't think so.
>
> And why would that be a problem?
>
> There is no reason to assume that there would be any overhead in storing a
> list of outstanding events for your connection compared to today.

Err, yes there would. Think about it: for that example to work, the
server would have to store every notify that happened until your
transaction completed. That could be thousands, considering you can
defer indefinitly. And at the end of your transaction it has to go
through the list and throw away 99% of them because they're not for
"foo" but for something else. Currently NOTIFY requires no storage at
all, so what you're suggesting is fairly expensive, since the cost
would be applied for every transaction, even ones that don't use
LISTEN.

The solution is to do the LISTEN first, outside the transaction. The
SELECT could be outside the transaction also, but you havn't told
enough to know if that's feasable.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.