Horton
Menu

Thread

  1. Allow LISTEN on patterns

    Quan Zongliang <quanzongliang@yeah.net> — 2025-03-04T04:39:45Z

    I implemented a LISTEN command that supports matching names in the LIKE 
format.

Just like

LISTEN 'c%';
NOTIFY c1;NOTIFY c2;

Notifications are received for c1 and c2.

For grammatical reasons, LISTEN 'v_'; with LISTEN v_; It's weird.

Should it be defined in a way that makes it easier to distinguish?
And support for more matching patterns.

For example
LISTEN [LIKE] 'like_pattern';
LISTEN SIMILAR 'regex_pattern';

--
Zongliang Quan

  2. Re: Allow LISTEN on patterns

    Trey Boudreau <trey@treysoft.com> — 2025-03-04T15:57:41Z

    > On Mar 3, 2025, at 10:39 PM, Quan Zongliang <quanzongliang@yeah.net> wrote:
> 
> I implemented a LISTEN command that supports matching names in the LIKE format.
> 
> Just like
> 
> LISTEN 'c%';
> NOTIFY c1;NOTIFY c2;
> 
> Notifications are received for c1 and c2.
> 
The parser down-cases ColId. Thus:

  LISTEN MiXeDcAsE;
  NOTIFY MIXEDCASE; — triggers notification

To which you’ve added:

  LISTEN ‘MiXeDcAsE%’;

Resulting in:

  NOTIFY MIXEDCASE; -- triggers original LISTEN, but not the pattern
  NOTIFY ‘MiXeDcAsE’; -- triggers only the pattern LISTEN, but not the original

Perhaps you want to use ILIKE instead of LIKE?

And then we have pg_notify(), which does NOT down-case the channel name, giving:

  PERFORM pg_notify(‘MiXeDcAsE’, ‘’); -- triggers only the pattern LISTEN :-(

The pg_notify() thing feels like a bug, given that historically NOTIFY takes only ColId as a parameter.

> For grammatical reasons, LISTEN 'v_'; with LISTEN v_; It's weird.
> 
> Should it be defined in a way that makes it easier to distinguish?
> And support for more matching patterns.
> 
> For example
> LISTEN [LIKE] 'like_pattern';
> LISTEN SIMILAR 'regex_pattern’;

Adding one of these existing key words seems preferable than to just predicating on the parsed object type.

You might have a look at [0] for fun to see what I tried recently,
— Trey

[0] https://www.postgresql.org/message-id/634685d67d0b491882169d2d0c084836%40treysoft.com

  3. Re: Allow LISTEN on patterns

    Aleksander Alekseev <aleksander@timescale.com> — 2025-03-05T14:03:46Z

    Hi,

> I implemented a LISTEN command that supports matching names in the LIKE
> format.

The overall idea seems reasonable. It would be nice to have such a
feature as long as it doesn't affect the performance of the existing
applications. I think we should discuss particular semantics though.
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?

Personally I would expect A, but I'm not sure how many people will agree.

-- 
Best regards,
Aleksander Alekseev

  4. Re: Allow LISTEN on patterns

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-05T16:42:48Z

    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.

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

  5. Re: Allow LISTEN on patterns

    Trey Boudreau <trey@treysoft.com> — 2025-03-05T17:13:23Z

    > On Mar 5, 2025, at 10:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Anyway, I encourage reading some of the past threads on this
> topic.
> 
I didn’t see any past references to the pg_notify() ‘anomaly’:

LISTEN FOO;
NOTIFY FOO, ‘BAR’; -- notification delivered
PERFORM pg_notify(‘FOO’, ‘BAR’); -- notification NOT delivered
PERFORM pg_notify(‘foo’, ‘BAR’); -- notification delivered

Can we come to some agreement on if we should consider this a bug?

— Trey

  6. Re: Allow LISTEN on patterns

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-03-05T17:21:25Z

    Does not seem like a bug to me. Just the normal auto-lowercase encountered
in every other SQL command. See:

greg=# select * from pg_listening_channels();
 pg_listening_channels
-----------------------
(0 rows)

greg=# listen foo;
LISTEN
greg=# select * from pg_listening_channels();
 pg_listening_channels
-----------------------
 foo
(1 row)

greg=# listen FOO;
LISTEN
greg=# select * from pg_listening_channels();
 pg_listening_channels
-----------------------
 foo
(1 row)

greg=# listen "FOO";
LISTEN
greg=# select * from pg_listening_channels();
 pg_listening_channels
-----------------------
 foo
 FOO
(2 rows)


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

  7. Re: Allow LISTEN on patterns

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-05T17:25:58Z

    Trey Boudreau <trey@treysoft.com> writes:
> I didn’t see any past references to the pg_notify() ‘anomaly’:

> LISTEN FOO;
> NOTIFY FOO, ‘BAR’; -- notification delivered
> PERFORM pg_notify(‘FOO’, ‘BAR’); -- notification NOT delivered
> PERFORM pg_notify(‘foo’, ‘BAR’); -- notification delivered

> Can we come to some agreement on if we should consider this a bug?

I don't think it's a bug particularly.  The actual channel name
being listened to is lowercase "foo", per the usual SQL identifier
case-folding rules.  But pg_notify is taking a literal not an
identifier, so you have to match case.

We do have some functions that downcase the input string unless
double-quoted, so that the experience is closer to what you get
for a SQL identifier.  Perhaps pg_notify should have done that
for the channel name, but it didn't and I think it's much too late
to revisit that.

			regards, tom lane

  8. Re: Allow LISTEN on patterns

    Quan Zongliang <quanzongliang@yeah.net> — 2025-03-05T22:43:54Z

    
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

  9. Re: Allow LISTEN on patterns

    Quan Zongliang <quanzongliang@yeah.net> — 2025-03-05T22:50:24Z

    
On 2025/3/4 23:57, Trey Boudreau wrote:
> 
>> On Mar 3, 2025, at 10:39 PM, Quan Zongliang <quanzongliang@yeah.net> 
>> wrote:
>>
>> I implemented a LISTEN command that supports matching names in the 
>> LIKE format.
>>
>> Just like
>>
>> LISTEN 'c%';
>> NOTIFY c1;NOTIFY c2;
>>
>> Notifications are received for c1 and c2.
>>
> The parser down-cases ColId. Thus:
> 
>    LISTEN MiXeDcAsE;
>    NOTIFY MIXEDCASE; — triggers notification
> 
> To which you’ve added:
> 
>    LISTEN ‘MiXeDcAsE%’;
> 
> Resulting in:
> 
>    NOTIFY MIXEDCASE; -- triggers original LISTEN, but not the pattern
>    NOTIFY ‘MiXeDcAsE’; -- triggers only the pattern LISTEN, but not the 
> original
> 
> Perhaps you want to use ILIKE instead of LIKE?
> 
> And then we have pg_notify(), which does NOT down-case the channel name, 
> giving:
> 
>    PERFORM pg_notify(‘MiXeDcAsE’, ‘’); -- triggers only the pattern 
> LISTEN :-(
> 
> The pg_notify() thing feels like a bug, given that historically NOTIFY 
> takes only ColId as a parameter.
> 
>> For grammatical reasons, LISTEN 'v_'; with LISTEN v_; It's weird.
>>
>> Should it be defined in a way that makes it easier to distinguish?
>> And support for more matching patterns.
>>
>> For example
>> LISTEN [LIKE] 'like_pattern';
>> LISTEN SIMILAR 'regex_pattern’;
> 
> Adding one of these existing key words seems preferable than to just 
> predicating on the parsed object type.
>
Yes, my considerations are superficial and need to be revisited.
I have replied in Tom's email.

> You might have a look at [0] for fun to see what I tried recently,
> — Trey
> 
> [0] https://www.postgresql.org/message- 
> id/634685d67d0b491882169d2d0c084836%40treysoft.com <https:// 
> www.postgresql.org/message- 
> id/634685d67d0b491882169d2d0c084836%40treysoft.com>
> 
>
Very good patch. Could you also consider adding "LISTEN ALL" and 
"UNLISTEN ALL"?
Users may feel more convenient and clear.