Re: LISTEN considered dangerous

On Thu, 3 Aug 2006, Martijn van Oosterhout wrote:

> > 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's exactly the same problem as exists today, although one transaction
earlier.

If someone listens to a bunch of events, does a commit and THEN waits
around forever then you still have to store all the events from that point
on.

The only difference is that it would have to happen from the start of the
transaction in stead of from the end of the transaction.

> That could be thousands, considering you can defer indefinitly.

Well, if the system still discards duplicate events then there is a
limit on how many events you can get.

Neighbouring transactions don't need to be kept around so they can be
merged into one.

In the end you end up with storing just one instance of the unique events
destined for each connection along with one instance of the unique events
generated during each transaction.

> 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.

Well, my application would probably be insterested in every single event,
but filtering for interesting events is really not a problem.

> 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.

Well, in my experience all transactions use listen, or none of them do.

You rarely have a situation where different clients access the same
database and even more rarely different clients that use different methods
to access it.

Either you need events when working with a given database instance or
you don't.

> The solution is to do the LISTEN first, outside the transaction.

Yes, this is the workaround I use.