Re: Allow LISTEN on patterns - Mailing list pgsql-hackers

On 2025/3/6 00:42, Tom Lane wrote:
> Aleksander Alekseev <aleksander@timescale.com> writes:
>> For instance, if I do:
> 
>> ```
>> LISTEN aaafoo;
>> LISTEN aaabar;
>> UNLISTEN aaa%;
>> ```
> 
>> Should I:
> 
>> A. be unsubscribed from aaafoo and aaabar since both match aaa% or
>> B. UNLISTEN should have no effect since I never subscribed to aaa% explicitly?
> 
> Yeah, the whole question of how LISTEN and UNLISTEN should interact
> was one of the stumbling blocks that previous proposals in this
> line ([1][2][3], and I think there were more) couldn't get past.
> Another interesting variant is
> 
>     LISTEN 'foo%';
>     UNLISTEN 'foobar%';
> 
> Does this leave you listening to foobazbar but not foobarbaz?
> That seems like it'd be what the user expects, but how can we
> implement that efficiently?  It seems like a series of N such
> commands could leave us in a state where we have to do N
> pattern-matches to decide if a channel name is being listened
> to, which seems annoyingly expensive.
> 
> Also consider the reverse case:
> 
>     LISTEN 'foobar%';
>     UNLISTEN 'foo%';
> 
> ISTM that this should leave you listening to nothing, but can
> we practically detect that, or are we going to end up doing
> two useless pattern matches for every incoming message?
> What this comes down to is whether we can recognize that one
> pattern is a superset of another.  That feels like it might
> be possible for simple LIKE patterns, but almost certainly
> not for regexes.
>
I am also aware of the problem. But I think we can leave it up to the 
user to manage these patterns. Because what they care about most is 
being able to listen to multiple channels through a single pattern. 
Since a NOTIFY is only received once, overlap between patterns has 
little effect.
The functions such as pg_listening_channels allows the user to see their 
listening settings. It helps them manage the listening. We also need to 
make some improvements to them.

> Anyway, I encourage reading some of the past threads on this
> topic.
> 
>             regards, tom lane
> 
> [1] https://www.postgresql.org/message-id/flat/A14CC639-E89D-4367-894D-883DBEC503B1%40treysoft.com
> [2]
https://www.postgresql.org/message-id/flat/CAN_hQmuysJpMzWcyhQwYtHpao8XXMpc48A8F%3Dn-0e6x_z2P_Fw%40mail.gmail.com
> [3] https://www.postgresql.org/message-id/flat/CAMpj9JbqhgQ5HjydoP0fovewQdOcu2c4RF5KKkH6J6ZNUjb2Rg%40mail.gmail.com
Sorry I didn't do the search before I did it.
If there is a clear syntax definition.  Users can clearly distinguish 
between them. Maybe we can move on.

LISTEN LIKE ‘c_’
will listening c1 c2, but not C1 C2.

LISTEN ILIKE ‘c_’
will listening c1 c2 C1 C2.

LISTEN SIMILAR TO ‘c[1-9]+’
will listening c1 c22, but not C1 C2.


The pg_listening_channels function helps users manage these listens.

select * from pg_listening_channels();
  type      | channels
------------------------
            | c1
  LIKE      | c_
  ILIKE     | c_
  SIILAR TO | c[1-9]+
  (4 rows)

Perhaps add a function to help users verify that they can currently 
listen to channels.

select * from pg_pattern_listening('like', 'c_');
  pg_pattern_listening
-----------------------
  c1
  c2
  (2 rows)

UNLISTEN can decide which listener to cancel based on the name alone.

UNLISTEN c_
Stop listening to channel "c_"

UNLISTEN 'c_'
Stop listening for channels that match pattern c_.
Since there are clear differences between several patterns. Doesn't seem 
to need more grammar.

If we want to stop listening to multiple channels at once. Think about more.
UNLISTEN LIKE 'c_'
Stop listening to channels c1 c2 and pattern c_, not to pattern c%

regards