Re: DISCARD ALL failing to acquire locks on pg_listen

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Feb 12, 2009 at 2:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Just for completeness, I attach another form of the patch that I thought
>>> about for a bit. This adds the ability for UNLISTEN ALL to revert the
>>> backend to the state where subsequent UNLISTENs don't cost anything.
>>> This could be of value in a scenario where you have pooled connections
>>> and just a small fraction of the client threads are using LISTEN. That
>>> seemed like kind of an unlikely use-case though. The problem is that
>>> this patch adds some cycles to transaction commit/abort for everyone,
>>> whether they ever use LISTEN/UNLISTEN/DISCARD or not. It's not a lot of
>>> cycles, but even so I'm thinking it's not a win overall. Comments?
>
>> This is so lightweight I'd be inclined to go for it, even if the use
>> case is pretty narrow. Do you think you can actually construct a
>> benchmark where the difference is measurable?
>
> Almost certainly not, but "a cycle saved is a cycle earned" ...
>
> The real problem I'm having with it is that I don't believe the
> use-case. The normal scenario for a listener is that you LISTEN and
> then you sit there waiting for events. In the above scenario, a client
> thread would only be able to receive events when it actively had control
> of its pool connection; so it seems like it would be at risk of missing
> things when it didn't. It seems much more likely that you'd design the
> application so that listening clients aren't pooled but are listening
> continuously. The guys sending NOTIFY events might well be pooled, but
> they're not the issue.
>
> If someone can show me a plausible use-case that gets a benefit from
> this form of the patch, I don't have a problem with making other people
> pay a few cycles for that. I'm just fearing that nobody would get a win
> at all, and then neither the cycles nor the extra complexity would give
> us any benefit. (The extra hooks into xact.c are actually bothering me
> as much as the cycles. Given that we're intending to throw all this
> code away and reimplement LISTEN/NOTIFY completely pretty soon, I'd just
> as soon keep down the number of contact points with the rest of the
> system.)

Imagine a web application interacting with a deamon using LISTEN/NOTIFY.
It happened in past to me to build one, so I guess it could be a fairly
common scenario, which you already described. Now if both the front end
and the deamon use the same pooler to have a common failover process,
previously listening connections could be reused by the web app if the
daemon is restarted and the pooler is not. Does it look plausible?

That said, I don't mind if we go with the previous two-liner fix :)

Cheers

--
Matteo Beccati

OpenX - http://www.openx.org