Allow LISTEN on patterns

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]/messages/by-id/634685d67d0b491882169d2d0c084836@treysoft.com for fun to see what I tried recently,
— Trey

[0]: /messages/by-id/634685d67d0b491882169d2d0c084836@treysoft.com

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

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]/messages/by-id/A14CC639-E89D-4367-894D-883DBEC503B1@treysoft.com[2]/messages/by-id/CAN_hQmuysJpMzWcyhQwYtHpao8XXMpc48A8F=n-0e6x_z2P_Fw@mail.gmail.com[3]/messages/by-id/CAMpj9JbqhgQ5HjydoP0fovewQdOcu2c4RF5KKkH6J6ZNUjb2Rg@mail.gmail.com, 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]: /messages/by-id/A14CC639-E89D-4367-894D-883DBEC503B1@treysoft.com
[2]: /messages/by-id/CAN_hQmuysJpMzWcyhQwYtHpao8XXMpc48A8F=n-0e6x_z2P_Fw@mail.gmail.com
[3]: /messages/by-id/CAMpj9JbqhgQ5HjydoP0fovewQdOcu2c4RF5KKkH6J6ZNUjb2Rg@mail.gmail.com

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

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

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

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] /messages/by-id/A14CC639-E89D-4367-894D-883DBEC503B1@treysoft.com
[2] /messages/by-id/CAN_hQmuysJpMzWcyhQwYtHpao8XXMpc48A8F=n-0e6x_z2P_Fw@mail.gmail.com
[3] /messages/by-id/CAMpj9JbqhgQ5HjydoP0fovewQdOcu2c4RF5KKkH6J6ZNUjb2Rg@mail.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

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.