Thread: Discussion on a LISTEN-ALL syntax

Howdy all,

NOTE:  Grey-beard coder, pgsql newbie. All info/tips/suggestions welcome!

I have a use-case where I’d like to LISTEN for all NOTIFY channels. Right now I simply
issue a LISTEN for every channel name of interest, but in production the channels will
number in the low thousands. The current implementation uses a linked list, and a linear
probe through the list of desired channels which will always return true becomes quite
expensive at this scale.

I have a work-around available by creating the “ALL” channel and making the payload
include the actual channel name, but this has a few of drawbacks:
 * it does not play nice with clients that actually want a small subset of channels;
 * it requires code modification at every NOTIFY;
 * it requires extra code on the client side.

The work-around subjects the developer (me :-) to significant risk of foot-gun disease,
so I'd like to propose a 'LISTEN *' equivalent to 'UNLISTEN *'.

The implementation in src/backend/commands/async.c seems straightforward enough, but it
feels prudent to select a syntax that doesn't make some kind of actual pattern matching
syntactically ugly in the future. Choosing 'LISTEN *' has a nice symmetry with 'UNLISTEN
*', but I don't have enough SQL chops to know if it cause problems.

If anyone has a better work-around, please speak up! If not, and we can come to some
resolution on a future-resistant syntax, I'd happily start working up a patch set.

Thanks,
-- Trey Boudreau

Trey Boudreau <trey@treysoft.com> writes:
> so I'd like to propose a 'LISTEN *' equivalent to 'UNLISTEN *'.

Seems reasonable in the abstract, and given the UNLISTEN * precedent
it's hard to quibble with that syntax choice.  I think what actually
needs discussing are the semantics, specifically how this'd interact
with other LISTEN/UNLISTEN actions.  Explain what you think should
be the behavior after:

LISTEN foo;
LISTEN *;
UNLISTEN *;
-- are we still listening on foo?

LISTEN *;
LISTEN foo;
UNLISTEN *;
-- how about now?

LISTEN *;
UNLISTEN foo;
-- how about now?

LISTEN *;
LISTEN foo;
UNLISTEN foo;
-- does that make a difference?

I don't have any strong preferences about this, but we ought to
have a clear idea of the behavior we want before we start coding.

            regards, tom lane

> On Dec 20, 2024, at 2:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Seems reasonable in the abstract, and given the UNLISTEN * precedent
> it's hard to quibble with that syntax choice.  I think what actually
> needs discussing are the semantics, specifically how this'd interact
> with other LISTEN/UNLISTEN actions.

My first pass at the documentation looks like this:

<para>
 The special wildcard <literal>*</literal> cancels all listener
 registrations for the current session and replaces them with a
 virtual registration that matches all channels. Further
 <command>LISTEN</command> and <command>UNLISTEN <replaceable
 class="parameter">channel</replaceable></command> commands will
 be ignored until the session sees the <command>UNLISTEN *</command>
 command.
</para>

> Explain what you think should
> be the behavior after:
>
> LISTEN foo;
> LISTEN *;
> UNLISTEN *;
> -- are we still listening on foo?
>
No, as the ‘LISTEN *’ wipes existing registrations.

> LISTEN *;
> LISTEN foo;
> UNLISTEN *;
> -- how about now?
Not listening on ‘foo’ or anything else.

> LISTEN *;
> UNLISTEN foo;
> -- how about now?
‘UNLISTEN foo’ ignored.

> LISTEN *;
> LISTEN foo;
> UNLISTEN foo;
> -- does that make a difference?
‘LISTEN foo’ and ‘UNLISTEN foo’ ignored, leaving only the wildcard.

> I don't have any strong preferences about this, but we ought to
> have a clear idea of the behavior we want before we start coding.
These semantics made sense to me, but I have limited experience and
a very specific use case in mind. Changing the behavior of ‘UNLISTEN *’
feels extremely impolite, and if we leave that alone I don’t see using
the ‘LISTEN *’ syntax with behavior that leaves other LISTENs in place.

We could have a different set of keywords, like LISTEN_ALL/UNLISTEN_ALL
that doesn’t interfere with the existing behavior.
-- Trey

Trey Boudreau <trey@treysoft.com> writes:
> My first pass at the documentation looks like this:

> <para>
>  The special wildcard <literal>*</literal> cancels all listener
>  registrations for the current session and replaces them with a
>  virtual registration that matches all channels. Further
>  <command>LISTEN</command> and <command>UNLISTEN <replaceable
>  class="parameter">channel</replaceable></command> commands will
>  be ignored until the session sees the <command>UNLISTEN *</command>
>  command.
> </para>

Hmph.  After thinking about it a bit I have a different idea
(and I see David has yet a third one).  So maybe this is more
contentious than it seems.  But at any rate, I have two
fundamental thoughts:

* "Listen to all but X" seems like a reasonable desire.

* The existing implementation already has the principle that
you can't listen to a channel more than once; that is,
    LISTEN foo;
    LISTEN foo;  -- this is a no-op, not a duplicate subscription

Therefore I propose:

* "LISTEN *" wipes away all previous listen state, and
sets up a state where you're listening to all channels
(within your database).

* "UNLISTEN *" wipes away all previous listen state, and
sets up a state where you're listening to no channels
(which is the same as it does now).

* "LISTEN foo" adds "foo" to what you are listening to,
with no effect if you already were listening to foo
(whether it was a virtual or explicit listen).

* "UNLISTEN foo" removes "foo" from what you are listening to,
with no effect if you already weren't listening to foo.

This is just about the same as the current behavior, and it makes
"LISTEN *" act the same as though you had somehow explicitly listed
every possible channel.  Which I think is a lot cleaner than
conceptualizing it as an independent gating behavior, as well
as more useful because it'll permit "all but" behavior.

The implementation of this could be something like

struct {
    bool    all;        /* true if listening to all */
    List   *plus;        /* channels explicitly listened */
    List   *minus;        /* channels explicitly unlistened */
} ListenChannels;

with the proviso that "plus" must be empty if "all" is true,
while "minus" must be empty if "all" is false.  The two lists
are always empty right after LISTEN * or UNLISTEN *, but could
be manipulated by subsequent channel-specific LISTEN/UNLISTEN.

(Since only one list would be in use at a time, you could
alternatively combine "plus" and "minus" into a single list
of exceptions to the all/none state.  I suspect that would
be confusingly error-prone to code; but perhaps it would turn
out elegantly.)


One other thing that needs to be thought about in any case
is what the pg_listening_channels() function ought to return
in these newly-possible states.

            regards, tom lane

Trey Boudreau <trey@treysoft.com> writes:

* "Listen to all but X" seems like a reasonable desire.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, December 20, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> * "Listen to all but X" seems like a reasonable desire.

> This I concur with, and would add: let me name my channels
> accounting.payables, accounting.receivables, sales.leads; and let me listen
> or ignore all accounting/sales channel names.

Hmm.  That reminds me that there was recently a proposal to allow
LISTEN/UNLISTEN with pattern arguments.  (It wasn't anything you'd
expect like regex patterns or LIKE patterns, but some off-the-wall
syntax, which I doubt we'd accept in that form.  But clearly there's
some desire for that out there.)

While I don't say we need to implement that as part of this,
it'd be a good idea to anticipate that that will happen.  And
that kind of blows a hole in my idea, because mine was predicated on
the assumption that you could unambiguously match UNLISTENs against
LISTENs.  A patterned UNLISTEN might revoke a superset or subset
of previous LISTENs, and I'm not sure you could readily tell which.

I think we can still hold to the idea that LISTEN * or UNLISTEN *
cancels all previous requests, but it's feeling like we might
have to accumulate subsequent requests without trying to make
contradictory ones cancel out.  Is it okay if the behavior is
explicitly dependent on the order of those requests, more or
less "last match wins"?  If not, how do we avoid that?

> As for exposing this to the user, this allow-all “channel” would be
> presented as any other normal channel.  The reader would need to know about
> the special meaning of whatever label we end up using. IOW, the wildcard is
> the label and no attempt to tie real in-use channel names to it should or
> even could be attempted.

Don't think that quite flies.  We might have to regurgitate the
state explicitly:

    LISTEN *
    UNLISTEN foo.*
    LISTEN foo.bar.*

showing that we're listening to channels foo.bar.*, but not other
channels beginning "foo", and also to all channels not beginning
"foo".

            regards, tom lane

> On 20 Dec 2024, at 23:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> ..it makes "LISTEN *" act the same as though you had somehow explicitly listed
> every possible channel.

When thinking about it while reading this thread, this is what I came up with
as well.  Since the current workings of LISTEN is so well established I can't
see how we could make this anything but a natural extension of the current.

--
Daniel Gustafsson

On 20/12/2024 23:45, Tom Lane wrote:
> Don't think that quite flies.  We might have to regurgitate the
> state explicitly:
>
>     LISTEN *
>     UNLISTEN foo.*
>     LISTEN foo.bar.*
>
> showing that we're listening to channels foo.bar.*, but not other
> channels beginning "foo", and also to all channels not beginning
> "foo".



Could I perhaps propose a sort of wildmat[1] syntax?

The above sequence could be expressed simply as:

     LISTEN *,!foo.*,foo.bar.*

I would like this in psql's backslash commands, too.


[1] https://en.wikipedia.org/wiki/Wildmat

-- 

Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:
> Could I perhaps propose a sort of wildmat[1] syntax?
> The above sequence could be expressed simply as:
>      LISTEN *,!foo.*,foo.bar.*

That doesn't absolve you from having to say what happens if the
user then issues another "LISTEN zed" or "UNLISTEN foo.bar.baz"
command.  We can't break the existing behavior that "LISTEN foo"
followed by "LISTEN bar" results in listening to both channels.
So on the whole this seems like it just adds complexity without
removing any.  I'm inclined to limit things to one pattern per
LISTEN/UNLISTEN command, with more complex behaviors reached
by issuing a sequence of commands.

            regards, tom lane

On 21/12/2024 05:23, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> Could I perhaps propose a sort of wildmat[1] syntax?
>> The above sequence could be expressed simply as:
>>       LISTEN *,!foo.*,foo.bar.*
> That doesn't absolve you from having to say what happens if the
> user then issues another "LISTEN zed" or "UNLISTEN foo.bar.baz"
> command.  We can't break the existing behavior that "LISTEN foo"
> followed by "LISTEN bar" results in listening to both channels.
> So on the whole this seems like it just adds complexity without
> removing any.  I'm inclined to limit things to one pattern per
> LISTEN/UNLISTEN command, with more complex behaviors reached
> by issuing a sequence of commands.


Fair enough.

-- 

Vik Fearing

> On Dec 20, 2024, at 4:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Friday, December 20, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> * "Listen to all but X" seems like a reasonable desire.
>
>> This I concur with, and would add: let me name my channels
>> accounting.payables, accounting.receivables, sales.leads; and let me listen
>> or ignore all accounting/sales channel names.
>
> Hmm.  That reminds me that there was recently a proposal to allow
> LISTEN/UNLISTEN with pattern arguments.  (It wasn't anything you'd
> expect like regex patterns or LIKE patterns, but some off-the-wall
> syntax, which I doubt we'd accept in that form.  But clearly there's
> some desire for that out there.)
>
I dug into the archives prior to starting this discussion. If folks really want
this then someone should probably promote the ‘ltree’ data type from contrib
to built-in and reuse the matching code. NOTIFY, LISTEN, and UNLISTEN
all use ‘ColId’ in the grammar, limiting patterns to NAMEDATALEN, and that
probably needs to change. I didn’t propose it because it seemed like too big
of a lift for a newbie project.

> While I don't say we need to implement that as part of this,
> it'd be a good idea to anticipate that that will happen.  And
> that kind of blows a hole in my idea, because mine was predicated on
> the assumption that you could unambiguously match UNLISTENs against
> LISTENs.  A patterned UNLISTEN might revoke a superset or subset
> of previous LISTENs, and I'm not sure you could readily tell which.
>
A version of LISTEN/UNLISTEN that accepts real patterns probably
wants a new keyword, like LISTEN_LTREE. If someone uses the new keyword
then they explicitly opt-out of non-pattern searches, perhaps?

> I think we can still hold to the idea that LISTEN * or UNLISTEN *
> cancels all previous requests, but it's feeling like we might
> have to accumulate subsequent requests without trying to make
> contradictory ones cancel out.  Is it okay if the behavior is
> explicitly dependent on the order of those requests, more or
> less "last match wins"?  If not, how do we avoid that?

I’d like a solution that doesn’t require walking the entire exception list. From
your earlier email I started sketching up something based on simplehash.h,
but that doesn’t lend itself to any sort of pattern matching. I don’t think you
can go too far down the road of resolving pattern matching conflicts until
we settle on the pattern matching technique. It feels like it will devolve to
dynamically assembling some kind of unified regex tree from the various
include/exclude patterns. I’d want to do a pretty serious literature search
to see if someone has already solved the problem.

Can/Should we stick to something simpler for now?
-- Trey