Re: Listen for all channel notifications
On 2024-12-24 13:09, Trey Boudreau wrote:
Based on [1], please find attached an implementation for listening
for notifications on all channels. The first two chunks lay some
groundwork and do not change the current LISTEN behavior, except
to improve performance of managing large numbers of channels.
Trying another email client to see if the patches show up.From de7ed45ebf5026a4a43857b38fbe3437db09db65 Mon Sep 17 00:00:00 2001
From: "Boudreau, Trey"
Date: Mon, 23 Dec 2024 14:18:44 -0600
Subject: [PATCH v1 1/3] Make simplehash more flexible.
Allow users of simplehash.h to have more control over the key when
inserting and removing items from the table. In particular one can now
say:
#define SH_KEY_TYPEconst char *
#define SH_MAKE_KEY(tb, k) MemoryContextStrdup(tb->ctx, k);
#define SH_UNMAKE_KEY(tb, k) pfree(k)
and usefully use char pointers for keys. If the user provides
SH_UNMAKE_KEY then SH_DELETE, SH_DELETE_ITEM, SH_RESET, and SH_DESTROY
will use it to process the stored keys prior to doing their work.
---
src/include/lib/simplehash.h | 40 ++--
1 file changed, 38 insertions(+), 2 deletions(-)
diff --git a/src/include/lib/simplehash.h b/src/include/lib/simplehash.h
index 3e1b1f9461..d349b25717 100644
--- a/src/include/lib/simplehash.h
+++ b/src/include/lib/simplehash.h
@@ -46,6 +46,11 @@
* are defined, so you can supply your own
* The following parameters are only relevant when SH_DEFINE is defined:
* - SH_KEY - name of the element in SH_ELEMENT_TYPE containing the hash key
+ * - SH_MAKE_KEY(table, key) - if defined, given a key passed to SH_LOOKUP,
+ * SH_LOOKUP_HASH, SH_INSERT, or SH_INSERT_HASH, return a value that can
+ * be assigned to an SH_KEY
+ * - SH_UNMAKE_KEY(table, stored_key) - if defined, process a stored key
+ * prior to removing the entry via SH_DELETE or SH_DELETE_ITEM
* - SH_EQUAL(table, a, b) - compare two table keys
* - SH_HASH_KEY(table, key) - generate hash for the key
* - SH_STORE_HASH - if defined the hash is stored in the elements
@@ -277,6 +282,28 @@ SH_SCOPE void SH_STAT(SH_TYPE * tb);
#define SH_GROW_MIN_FILLFACTOR 0.1
#endif
+#ifndef SH_MAKE_KEY
+#define SH_MAKE_KEY(table, key) key
+#endif
+
+#ifdef SH_UNMAKE_KEY
+#define SH_UNMAKE_ALL_KEYS(tb) \
+ if (tb->members) \
+ { \
+ SH_ELEMENT_TYPE *elem = tb->data; \
+ SH_ELEMENT_TYPE *end = elem + tb->size; \
+ while (elem < end) \
+ { \
+ if (elem->status == SH_STATUS_IN_USE) \
+SH_UNMAKE_KEY(tb, elem->SH_KEY); \
+ elem++; \
+ } \
+ }
+#else
+#define SH_UNMAKE_KEY(table, key) do {} while (false)
+#define SH_UNMAKE_ALL_KEYS(table) do {} while (false)
+#endif
+
#ifdef SH_STORE_HASH
#define SH_COMPARE_KEYS(tb, ahash, akey, b) (ahash == SH_GET_HASH(tb, b) && SH_EQUAL(tb, b->SH_KEY, akey))
#else
@@ -471,6 +498,7 @@ SH_CREATE(MemoryContext ctx, uint32 nelements, void *private_data)
SH_SCOPE void
SH_DESTROY(SH_TYPE * tb)
{
+ SH_UNMAKE_ALL_KEYS(tb);
SH_FREE(tb, tb->data);
pfree(tb);
}
@@ -479,6 +507,7 @@ SH_DESTROY(SH_TYPE * tb)
SH_SCOPE void
SH_RESET(SH_TYPE * tb)
{
+ SH_UNMAKE_ALL_KEYS(tb);
memset(tb->data, 0, sizeof(SH_ELEMENT_TYPE) * tb->size);
tb->members = 0;
}
@@ -652,7 +681,7 @@ restart:
if (entry->status == SH_STATUS_EMPTY)
{
tb->members++;
- entry->SH_KEY = key;
+ entry->SH_KEY = SH_MAKE_KEY(tb, key);
#ifdef SH_STORE_HASH
SH_GET_HASH(tb, entry) = hash;
#endif
@@ -740,7 +769,7 @@ restart:
/* and fill the now empty spot */
tb->members++;
- entry->SH_KEY = key;
+ entry->SH_KEY = SH_MAKE_KEY(tb, key);
#ifdef SH_STORE_HASH
SH_GET_HASH(tb, entry) = hash;
#endif
@@ -872,6 +901,8 @@ SH_DELETE(SH_TYPE * tb, SH_KEY_TYPE key)
{
SH_ELEMENT_TYPE *lastentry = entry;
+ SH_UNMAKE_KEY(tb, entry->SH_KEY);
+
tb->members--;
/*
@@ -935,6 +966,8 @@ SH_DELETE_ITEM(SH_TYPE * tb, SH_ELEMENT_TYPE * entry)
/* Calculate the index of 'entry' */
curelem = entry - &tb->data[0];
+ SH_UNMAKE_KEY(tb, entry->SH_KEY);
+
tb->members--;
/*
@@ -1148,6 +1181,9 @@ SH_STAT(SH_TYPE * tb)
#undef SH_KEY_TYPE
#undef SH_KEY
#undef SH_ELEMENT_TYPE
+#undef SH_MAKE_KEY
+#undef SH_UNMAKE_KEY
+#undef SH_UNMAKE_ALL_KEYS
#undef SH_HASH_KEY
#undef SH_SCOPE
#undef SH_DECLARE
--
2.43.0
From 258d75348952c28d8ff3950363471c48e8a8c362 Mon Sep 17 00:00:00 2001
From: "Boudreau, Trey"
Date: Tue, 24 Dec 2024 12:15:36 -0600
Subject: [PATCH v1 3/3] WIP Allow LISTENing for all channels.
Extend the notion of LISTENing to include all channels via
'LISTEN *', as per:
https://www.postgresql.org/message-id/737106.1734732462%40sss.pgh.pa.us
This commit does not change the results of pg_listening_channels(),
but some future version of it should.
---
doc/src/sgml/ref/listen.sgml| 37 +--
src/backend/commands/async.c| 149 ++
Re: Discussion on a LISTEN-ALL syntax
> On Dec 20, 2024, at 2:58 PM, Tom Lane 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: The special wildcard * cancels all listener registrations for the current session and replaces them with a virtual registration that matches all channels. Further LISTEN and UNLISTEN channel commands will be ignored until the session sees the UNLISTEN * command. > 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
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
Re: Discussion on a LISTEN-ALL syntax
> On Dec 20, 2024, at 4:07 PM, Tom Lane wrote: > > 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. > I have an implementation of this that replaces List with a simplehash.h variant, merging 'plus/minus' as ‘exceptions’. > 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. > My previous cut at this replaced the list with ‘*’, but since we now allow exceptions, how about preceding the list with ‘*” in the Want-all case, following with the list of exceptions? In another branch of this discussion covering patterns I mentioned building a tree of regular expressions. If we go with the notion of ‘want-all/want-none, with exceptions’ then we could introduce a function like ‘pg_listens_use_regexes(bool)’. When true we’d build a pre-parsed regex from the exception list by encapsulating the patterns in something like ‘(^’‘$)’ and aggregating with ‘|’. We could alternatively have ‘pg_listen_pattern(style)’, with style choices of IDENT (current behavior), REGEX, LTREE, LIKE, etc. So long as we treated all of the exceptions as the same type it seems pretty sane. Allowing mixing would take lots of work. -- Trey
Re: Discussion on a LISTEN-ALL syntax
> On Dec 20, 2024, at 4:45 PM, Tom Lane wrote: > > "David G. Johnston" writes: >> On Friday, December 20, 2024, Tom Lane 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
Listen for all channel notifications
Based on [1], please find attached an implementation for listeningfor notifications on all channels. The first two chunks lay somegroundwork and do not change the current LISTEN behavior, exceptto improve performance of managing large numbers of channels.v1-0001-Make-simplehash-more-flexible.patch makes working withchar pointer keys possible.v1-0002-Improve-LISTEN-NOTIFY-UNLISTEN-performance.patch replacesthe current List implementation with a simplehash.h-based table.v1-0003-WIP-Allow-LISTENing-for-all-channels.patch has the real meatbut needs further work, as the results from pg_listening_channels()provides no clue that, after 'LISTEN *', the results list exceptionsrather than notifiable channels.[1]: https://www.postgresql.org/message-id/737106.1734732462%40sss.pgh.pa.us-- Trey v1-0001-Make-simplehash-more-flexible.patch Description: Binary data v1-0002-Improve-LISTEN-NOTIFY-UNLISTEN-performance.patch Description: Binary data v1-0003-WIP-Allow-LISTENing-for-all-channels.patch Description: Binary data
Re: Allow LISTEN on patterns
> On Mar 3, 2025, at 10:39 PM, Quan Zongliang 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
Re: Allow LISTEN on patterns
> On Mar 5, 2025, at 10:42 AM, Tom Lane 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
