Add pattern matching support for LISTEN/NOTIFY channels

Hi hackers,

I'd like to propose for PG 20 adding glob-style pattern matching support
for LISTEN channel names, allowing clients to subscribe to multiple
channels with a single LISTEN command using wildcards.

Currently, if an application wants to receive notifications from multiple
related channels (e.g., user_1, user_2, ..., user_N), it must issue a
separate LISTEN command for each channel. This becomes cumbersome for
applications that need to monitor a dynamic or large set of channels with
a common naming convention.

With pattern support, a client could simply do LISTEN "user_*" and receive
notifications from any channel matching that pattern such as user_login,
user_logout, user_123, and so on.

The attached patch adds support for glob-style wildcards in channel
names. The * matches zero or more characters, the ? matches exactly one
character, and \ escapes the next character to match literal * or ?.

For example, LISTEN "events_*" matches events_created, events_deleted, etc.
LISTEN "sensor_?" matches sensor_1 or sensor_A but not sensor_12.
LISTEN "app_*_status" matches app_web_status, app_worker_status, and similar.
LISTEN "literal\*star" matches the literal channel name "literal*star".

This is a proof-of-concept implementation using the simplest approach I could
think of to validate the behavior and gather feedback before investing in
optimizations.

Pattern channels are stored in a backend-local list rather than the
global shared hash table, since they cannot be looked up by exact
channel name. A new hasPatterns flag in QueueBackendStatus tracks
whether a backend is listening on any patterns. When signaling backends,
those with hasPatterns=true are always woken for notifications in their
database, so they can check if any notifications match their patterns
locally. The MatchPattern() function implements a backtracking algorithm
to handle the * wildcard matching.

Performance Considerations:

The current implementation has O(PxM) complexity for pattern matching,
where P is the number of patterns and M is the pattern matching cost.
For each notification, backends with patterns must check all their
patterns against the channel name.

I ran some benchmarks comparing exact channel matching vs pattern matching
(Python script created with Claude help attached). Results on my machine:

Test 1: Exact Channel vs Pattern Channel
----------------------------------------------------------------------
Benchmark Notif/sec
Exact channel (LISTEN "user_123") 13176 (baseline)
Pattern channel (LISTEN "user_*") 13153 (-0.2%)

Test 2: Pattern Count Scaling
----------------------------------------------------------------------
Benchmark Notif/sec
1 pattern(s) 13289 (baseline)
5 pattern(s) 13279 (-0.1%)
10 pattern(s) 13268 (-0.2%)
25 pattern(s) 13301 (+0.1%)
50 pattern(s) 13287 (-0.0%)
100 pattern(s) 13211 (-0.6%)

Test 3: Pattern Complexity
----------------------------------------------------------------------
Benchmark Notif/sec
Prefix pattern (user_*) 13446 (baseline)
Single char pattern (user_?) 13179 (-2.0%)
Infix pattern (prefix_*_suffix) 13469 (+0.2%)
Multi-wildcard (a_*_b_*_c) 13200 (-1.8%)

Test 4: Non-Matching Pattern Overhead
----------------------------------------------------------------------
Benchmark Notif/sec
Exact only (baseline) 13141 (baseline)
Exact + 10 non-matching patterns 13098 (-0.3%)
Exact + 50 non-matching patterns 13130 (-0.1%)
Exact + 100 non-matching patterns 12742 (-3.0%)

The benchmark spawns a listener connection and a notifier connection,
then measures how many notifications per second can be delivered. Each
measurement runs for 15 seconds and is repeated 3 times.

The results don't show a huge degradation but perhaps more comprehensive
benchmarks with higher concurrency and more realistic workloads would be
valuable to better understand the performance behavior.

Future Improvements:

If this idea seems reasonable, the pattern match can be optimized using
a more sophisticated data structure or improve the current patch idea.
One option is to follow the current architecture, keeping patterns
separate from exact-match channels, but finding optimizations to make
pattern matching faster. I'm wondering if we could have a sorted list
and try to do some kind of binary search with patterns, but I dind't
explore this idea further.

Another option is a Trie (prefix tree) [1]. You walk the trie with the
channel name and any node marked as a wildcard indicates a match. For
mid-pattern wildcards like "prefix_*_suffix", when you hit the * node
you search for the remaining suffix in the input. A Trie could
potentially unify exact-match channels and patterns in the same
structure, where exact channels are simply terminal nodes without the
wildcard flag. But memory overhead can be significant for sparse
patterns and also I'm not sure how this would fit using shared memory,
so perhaps a trie for each backend listening on patterns alongside with
the global shared memory for non-pattern channels will be better.

Feedback welcome!

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

--
Matheus Alcantara
EDB: https://www.enterprisedb.com