Hi Emanuel,
Changed implementation of the function Exec_UnlistenCommit . v2 of the path
contained a bug in the function Exec_UnlistenCommit (added a test case for
that) and also it was not implemented in natural to C form using pointers.
Now it looks fine and works as expected.
In the previous email I forgot to mention that the new implementation of
the function Exec_UnlistenCommit has the same space and time complexities
as the original implementation (which doesn't support wildcards).
Regards,
Alexander Cheshev
On Sat, 13 Jul 2024 at 13:26, Alexander Cheshev <alex.ches...@gmail.com>
wrote:
> Hi Emanuel,
>
> I did a test over the "UNLISTEN >" behavior, and I'm not sure if this is
>> expected.
>> This command I assume should free all the listening channels, however, it
>> doesn't
>> seem to do so:
>
>
> TODO “Allow LISTEN on patterns” [1] is a bit vague about that feature. So
> I didn't implement it in the first version of the patch. Also I see that I
> made a mistake in the documentation and mentioned that it is actually
> supported. Sorry for the confusion.
>
> Besides obvious reasons I think that your finding is especially attractive
> for the following reason. We have an UNLISTEN * command. If we replace >
> with * in the patch (which I actually did in the new version of the patch)
> then we have a generalisation of the above command. For example, UNLISTEN
> a* cancels registration on all channels which start with a.
>
> I attached to the email the new version of the patch which supports the
> requested feature. Instead of > I use * for the reason which I mentioned
> above. Also I added test cases, changed documentation, etc.
>
> I appreciate your work, Emanuel! If you have any further findings I will
> be glad to adjust the patch accordingly.
>
> [1]
> https://www.postgresql.org/message-id/flat/52693FC5.7070507%40gmail.com
>
> Regards,
> Alexander Cheshev
>
> Regards,
> Alexander Cheshev
>
>
> On Tue, 9 Jul 2024 at 11:01, Emanuel Calvo <3man...@gmail.com> wrote:
>
>>
>> Hello there,
>>
>>
>> El vie, 15 mar 2024 a las 9:01, Alexander Cheshev (<
>> alex.ches...@gmail.com>) escribió:
>>
>>> Hello Hackers,
>>>
>>> I have implemented TODO “Allow LISTEN on patterns” [1] and attached
>>> the patch to the email. The patch basically consists of the following
>>> two parts.
>>>
>>> 1. Support wildcards in LISTEN command
>>>
>>> Notification channels can be composed of multiple levels in the form
>>> ‘a.b.c’ where ‘a’, ‘b’ and ‘c’ are identifiers.
>>>
>>> Listen channels can be composed of multiple levels in the form ‘a.b.c’
>>> where ‘a’, ‘b’ and ‘c’ are identifiers which can contain the following
>>> wildcards:
>>> * ‘%’ matches everything until the end of a level. Can only appear
>>> at the end of a level. For example, the notification channels ‘a.b.c’,
>>> ‘a.bc.c’ match against the listen channel ‘a.b%.c’.
>>> * ‘>’ matches everything to the right. Can only appear at the end of
>>> the last level. For example, the notification channels ‘a.b’, ‘a.bc.d’
>>> match against the listen channel ‘a.b>’.
>>>
>>>
>> I did a test over the "UNLISTEN >" behavior, and I'm not sure if this is
>> expected.
>> This command I assume should free all the listening channels, however, it
>> doesn't
>> seem to do so:
>>
>> postgres=# LISTEN device1.alerts.%;
>> LISTEN
>> postgres=# ;
>> Asynchronous notification "device1.alerts.temp" with payload "80"
>> received from server process with PID 237.
>> postgres=# UNLISTEN >;
>> UNLISTEN
>> postgres=# ; -- Here I send a notification over the same channel
>> Asynchronous notification "device1.alerts.temp" with payload "80"
>> received from server process with PID 237.
>>
>> The same happens with "UNLISTEN %;", although I'm not sure if this should
>> have
>> the same behavior.
>>
>> It stops listening correctly if I do explicit UNLISTEN (exact channel
>> matching).
>>
>> I'll be glad to conduct more tests or checks on this.
>>
>> Cheers,
>>
>>
>> --
>> --
>> Emanuel Calvo
>> Database Engineering
>> https://tr3s.ma/aobut
>>
>>
From 403a432c129084ac4f17a92e29aa5398de0125f0 Mon Sep 17 00:00:00 2001
From: Alexander Cheshev <alex.ches...@gmail.com>
Date: Thu, 14 Mar 2024 21:53:29 +0100
Subject: [PATCH v3] Support wildcards in LISTEN command
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Notification channels can be composed of multiple levels in the form ‘a.b.c’ where ‘a’, ‘b’ and ‘c’ are identifiers.
Listen and unlisten channels can be composed of multiple levels in the form ‘a.b.c’ where ‘a’, ‘b’ and ‘c’ are identifiers which can contain the following wildcards:
* The wildcard ‘%’ matches everything until the end of a level. Can only appear at the end of a level. For example, the notification channels ‘a.b.c’, ‘a.bc.c’ match against the notification channel ‘a.b%.c’.
* The wildcard ‘*’ matches everything to the right. Can only appear at the end of the last level. For example, the notification channels ‘a.b’, ‘a.bc.d’ match against the notification channel ‘a.b*’.
Use binary trie to match notification channels against listen channels.
---
doc/src/sgml/ref/listen.sgml | 41 +-
doc/src/sgml/ref/notify.sgml | 9 +-
doc/src/sgml/ref/unlisten.sgml | 46 +-
src/backend/commands/async.c | 520 ++++++++++++++++++-
src/backend/parser/gram.y | 52 +-
src/backend/tcop/utility.c | 5 +-
src/test/isolation/expected/async-notify.out | 184 ++++++-
src/test/isolation/specs/async-notify.spec | 50 ++
src/test/regress/expected/async.out | 115 +++-
src/test/regress/sql/async.sql | 63 ++-
10 files changed, 1040 insertions(+), 45 deletions(-)
diff --git a/doc/src/sgml/ref/listen.sgml b/doc/src/sgml/ref/listen.sgml
index 6c1f09bd45..72dde0943d 100644
--- a/doc/src/sgml/ref/listen.sgml
+++ b/doc/src/sgml/ref/listen.sgml
@@ -36,6 +36,16 @@ LISTEN <replaceable class="parameter">channel</replaceable>
this notification channel, nothing is done.
</para>
+ <para>
+ Channel names can be composed of multiple levels in the form
+ <literal>a.b.c</literal> which allows you to organise channel names in
+ hierarchy. Listen channels can contain wildcards which match multiple
+ notification channels. For example, the notification channels
+ <literal>a.b.c</literal>, <literal>a.bc.c</literal> match against the
+ listen channel <literal>a.b%.c</literal> (<literal>%</literal> matches
+ everything until the end of the level).
+ </para>
+
<para>
Whenever the command <command>NOTIFY <replaceable
class="parameter">channel</replaceable></command> is invoked, either
@@ -73,8 +83,37 @@ LISTEN <replaceable class="parameter">channel</replaceable>
<term><replaceable class="parameter">channel</replaceable></term>
<listitem>
<para>
- Name of a notification channel (any identifier).
+ Name of a notification channel. It is a simple string composed of
+ multiple levels in the form <literal>a.b.c</literal> where
+ <literal>a</literal>, <literal>b</literal> and <literal>c</literal> are
+ identifiers which can contain the following wildcards:
</para>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>%</literal></term>
+ <listitem>
+ <para>
+ Matches everything until the end of a level. Can only appear at
+ the end of a level. For example, <command>LISTEN a.b%.c</command>
+ registers on the notification channels named like
+ <literal>a.b.c</literal>, <literal>a.bc.c</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>*</literal></term>
+ <listitem>
+ <para>
+ Matches everything to the right. Can only appear at the end of
+ the last level. For example, <command>LISTEN a.b*</command>
+ registers on the notification channels named like
+ <literal>a.b</literal>, <literal>a.bc.d</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</listitem>
</varlistentry>
</variablelist>
diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml
index fd6ed54e8f..05a1bc6c97 100644
--- a/doc/src/sgml/ref/notify.sgml
+++ b/doc/src/sgml/ref/notify.sgml
@@ -55,7 +55,9 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
<para>
It is up to the database designer to define the channel names that will
- be used in a given database and what each one means.
+ be used in a given database and what each one means. The channel names can be
+ composed of multiple levels in the form <literal>a.b.c</literal> which
+ allows you to organise them in hierarchy.
Commonly, the channel name is the same as the name of some table in
the database, and the notify event essentially means, <quote>I changed this table,
take a look at it to see what's new</quote>. But no such association is enforced by
@@ -131,7 +133,10 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
<term><replaceable class="parameter">channel</replaceable></term>
<listitem>
<para>
- Name of the notification channel to be signaled (any identifier).
+ Name of the notification channel to be signaled. It is
+ a simple string composed of multiple levels in the form
+ <literal>a.b.c</literal> where <literal>a</literal>,
+ <literal>b</literal> and <literal>c</literal> are identifiers.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/unlisten.sgml b/doc/src/sgml/ref/unlisten.sgml
index 687bf485c9..0f37ec618f 100644
--- a/doc/src/sgml/ref/unlisten.sgml
+++ b/doc/src/sgml/ref/unlisten.sgml
@@ -34,10 +34,12 @@ UNLISTEN { <replaceable class="parameter">channel</replaceable> | * }
<command>UNLISTEN</command> cancels any existing registration of
the current <productname>PostgreSQL</productname> session as a
listener on the notification channel named <replaceable
- class="parameter">channel</replaceable>. The special wildcard
- <literal>*</literal> cancels all listener registrations for the
+ class="parameter">channel</replaceable>. Channel name
+ <replaceable class="parameter">channel</replaceable> can contain
+ wildcards which allows you to simultaneously cancel multiple
+ registrations for the current session. For example,
+ <command>UNLISTEN *</command> cancels all registrations for the
current session.
- </para>
<para>
<xref linkend="sql-notify"/>
@@ -55,17 +57,37 @@ UNLISTEN { <replaceable class="parameter">channel</replaceable> | * }
<term><replaceable class="parameter">channel</replaceable></term>
<listitem>
<para>
- Name of a notification channel (any identifier).
+ Name of a notification channel. It is a simple string composed of
+ multiple levels in the form <literal>a.b.c</literal> where
+ <literal>a</literal>, <literal>b</literal> and <literal>c</literal> are
+ identifiers which can contain the following wildcards:
</para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><literal>*</literal></term>
- <listitem>
- <para>
- All current listen registrations for this session are cleared.
- </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>%</literal></term>
+ <listitem>
+ <para>
+ Matches everything until the end of a level. Can only appear at
+ the end of a level. For example, <command>UNLISTEN a.b%.c</command>
+ cancels registrations on the notification channels named like
+ <literal>a.b.c</literal>, <literal>a.bc.c</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>*</literal></term>
+ <listitem>
+ <para>
+ Matches everything to the right. Can only appear at the end of
+ the last level. For example, <command>UNLISTEN a.b*</command>
+ cancels registrations on the notification channels named like
+ <literal>a.b</literal>, <literal>a.bc.d</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</listitem>
</varlistentry>
</variablelist>
diff --git a/src/backend/commands/async.c b/src/backend/commands/async.c
index ab4c72762d..133c29f9a7 100644
--- a/src/backend/commands/async.c
+++ b/src/backend/commands/async.c
@@ -319,6 +319,46 @@ static SlruCtlData NotifyCtlData;
*/
static List *listenChannels = NIL; /* list of C strings */
+/*
+ * Channel names consist of multiple levels which are separated by the
+ * character '.'. For example, 'a.b.c'. Listen channels can contain the
+ * following wildcards to match against multiple notification channels:
+ * 1. The wildcard '%' matches everything until the end of the level. For
+ * example, 'aa.b' matches against 'a%.b'.
+ * 2. The wildcard '*' matches everything until the end of the notification
+ * channel. For example, 'a.b.c' matches against 'a.*'.
+ */
+#define MATCH_OP_LEVEL(c) ((c) == '.' || (c) == '\0')
+#define MATCH_OP_WILDRIGHT(c) ((c) == '*')
+#define MATCH_OP_WILDLEVEL(c) ((c) == '%')
+#define MATCH_OP_NOWILD(c) ((c) != '*' && (c) != '%')
+
+/*
+ * Returns the number of trailing 0-bits in char starting at the least
+ * significant bit position. If char is 0 the result is CHAR_BIT.
+ */
+#define MATCH_CTZC(c) (pg_rightmost_one_pos32(1 << CHAR_BIT | (c)))
+
+/* Node in the binary trie of the listen channels */
+typedef struct TrieNode
+{
+ const char *channel; /* Listen channel, C string */
+ unsigned int index; /* The index of the least significant bit on
+ * which the listen channel on the left
+ * differs from the listen channel on the
+ * right */
+ unsigned int length; /* Number of bits in the listen channel
+ * including the last character '\0' */
+ struct TrieNode *left; /* The left child */
+ struct TrieNode *right; /* The right child */
+} TrieNode;
+
+/*
+ * The root of the binary trie which is used to match notification channels
+ * against the listen channels. It is allocated in TopMemoryContext.
+ */
+static TrieNode * matchingTrie = NULL;
+
/*
* State for pending LISTEN/UNLISTEN actions consists of an ordered list of
* all actions requested in the current transaction. As explained above,
@@ -457,6 +497,14 @@ static void AddEventToPendingNotifies(Notification *n);
static uint32 notification_hash(const void *key, Size keysize);
static int notification_match(const void *key1, const void *key2, Size keysize);
static void ClearPendingActionsAndNotifies(void);
+static bool IsMatchingOn(const char *channel);
+static void BuildMatchingTrie(void);
+static void DeleteMatchingTrie(void);
+static void FreeMatchingTrieRecursively(TrieNode * node);
+static bool IsTrieMatchingOnRecursively(const char *channel,
+ TrieNode * node,
+ size_t channelIndex,
+ size_t parentIndex);
/*
* Compute the difference between two queue page numbers.
@@ -764,7 +812,7 @@ Async_Unlisten(const char *channel)
/*
* Async_UnlistenAll
*
- * This is invoked by UNLISTEN * command, and also at backend exit.
+ * This is invoked at backend exit.
*/
void
Async_UnlistenAll(void)
@@ -822,6 +870,7 @@ pg_listening_channels(PG_FUNCTION_ARGS)
static void
Async_UnlistenOnExit(int code, Datum arg)
{
+ DeleteMatchingTrie();
Exec_UnlistenAllCommit();
asyncQueueUnregister();
}
@@ -1001,6 +1050,13 @@ AtCommit_Notify(void)
}
}
+ /*
+ * Build the matching trie which is used to match notification channels
+ * against the listen channels
+ */
+ if (pendingActions != NULL)
+ BuildMatchingTrie();
+
/* If no longer listening to anything, get out of listener array */
if (amRegisteredListener && listenChannels == NIL)
asyncQueueUnregister();
@@ -1170,12 +1226,52 @@ Exec_UnlistenCommit(const char *channel)
foreach(q, listenChannels)
{
char *lchan = (char *) lfirst(q);
+ const char *s1 = channel;
+ const char *s2 = lchan;
- if (strcmp(lchan, channel) == 0)
+ while (*s1 != '\0')
+ {
+ if (MATCH_OP_WILDRIGHT(*s1))
+ {
+ /* lchan is a subset of channel, remove lchan from the list */
+ s1++;
+ s2 = s1;
+ break;
+ }
+ else if (MATCH_OP_WILDRIGHT(*s2))
+ {
+ /*
+ * channel is a subset of lchan, break the loop and consider
+ * the next lchan
+ */
+ break;
+ }
+ else if (MATCH_OP_WILDLEVEL(*s1) &&
+ MATCH_OP_LEVEL(*s2))
+ s1++;
+ else if (MATCH_OP_WILDLEVEL(*s1) &&
+ MATCH_OP_NOWILD(*s2))
+ s2++;
+ else
+ {
+ if (*s1 != *s2)
+ {
+ /*
+ * lchan is not a subset of channel, break the loop and
+ * consider the next lchan
+ */
+ break;
+ }
+
+ s1++;
+ s2++;
+ }
+ }
+
+ if (*s1 == '\0' && *s2 == '\0')
{
listenChannels = foreach_delete_current(listenChannels, q);
pfree(lchan);
- break;
}
}
@@ -1203,10 +1299,8 @@ Exec_UnlistenAllCommit(void)
/*
* Test whether we are actively listening on the given channel name.
*
- * Note: this function is executed for every notification found in the queue.
- * Perhaps it is worth further optimization, eg convert the list to a sorted
- * array so we can binary-search it. In practice the list is likely to be
- * fairly short, though.
+ * Note: this function is not used to match notification channels against
+ * the listen channels so there is not need to optimize it any further.
*/
static bool
IsListeningOn(const char *channel)
@@ -2071,7 +2165,7 @@ asyncQueueProcessPageEntries(volatile QueuePosition *current,
/* qe->data is the null-terminated channel name */
char *channel = qe->data;
- if (IsListeningOn(channel))
+ if (IsMatchingOn(channel))
{
/* payload follows channel name */
char *payload = qe->data + strlen(channel) + 1;
@@ -2395,3 +2489,413 @@ check_notify_buffers(int *newval, void **extra, GucSource source)
{
return check_slru_buffers("notify_buffers", newval);
}
+
+/*
+ * Match the notification channel against the listen channels
+ */
+static bool
+IsMatchingOn(const char *channel)
+{
+ return IsTrieMatchingOnRecursively(channel, matchingTrie, 0, 0);
+}
+
+/*
+ * Build the binary trie of the listen channels which is used to match
+ * notification channels against the listen channels. The time complexity can
+ * be estimated as O(nm) where n is the number of the listen channels and m
+ * is the maximum length among the listen channels. As space complexity is
+ * dominated by the leaf nodes it can be estimated as O(n) where n is the
+ * number of the listen channels. The function builds the matching trie as a
+ * usual binary trie except fo the following two cases:
+ * 1. If a parent node satisfies the following condition:
+ * channel[parent->index / CHAR_BIT] == '%'
+ * then the descendants on the left don't satisfy the condition and the
+ * descendants on the right satisfy the condition.
+ * 2. If a parent node satisfies the following condition:
+ * channel[parent->index / CHAR_BIT] == '*'
+ * then either the parent node doesn't have children or it has only the
+ * left child which we solely preserve to free the memory during the next
+ * build of the matching trie.
+ */
+static void
+BuildMatchingTrie()
+{
+ ListCell *p;
+ MemoryContext oldcontext;
+
+ DeleteMatchingTrie();
+
+ /*
+ * Allocate the matching trie in the TopMemoryContext as the listen
+ * channels are allocated in there
+ */
+ oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+
+ foreach(p, listenChannels)
+ {
+ char *lchan = (char *) lfirst(p);
+ size_t i = 0;
+ size_t l = strlen(lchan) + 1;
+ size_t n = l * CHAR_BIT;
+ size_t k = 0;
+ size_t r;
+ char x;
+ TrieNode **prev;
+ TrieNode *next;
+
+ if (matchingTrie == NULL)
+ {
+ matchingTrie = (TrieNode *) palloc(sizeof(TrieNode));
+ matchingTrie->channel = lchan;
+ matchingTrie->index = n;
+ matchingTrie->length = l;
+ matchingTrie->left = NULL;
+ matchingTrie->right = NULL;
+ continue;
+ }
+
+ prev = &matchingTrie;
+ next = matchingTrie;
+ while (i < n)
+ {
+ if (MATCH_OP_WILDRIGHT(next->channel[k]))
+ {
+ /* The listen channel is a subset of the trie channel */
+ break;
+ }
+ else if (MATCH_OP_WILDRIGHT(lchan[k]))
+ {
+ /*
+ * The trie channel is a subset of the listen channel. So
+ * replace the trie channel with the listen channel. We solely
+ * preserve the next node to free the memory during the next
+ * build of the matching trie.
+ */
+ TrieNode *parent;
+
+ parent = palloc(sizeof(TrieNode));
+ parent->channel = lchan;
+ parent->index = n;
+ parent->length = l;
+ parent->left = next;
+ parent->right = NULL;
+
+ *prev = parent;
+
+ break;
+ }
+ else if (MATCH_OP_WILDLEVEL(next->channel[k]) &&
+ !MATCH_OP_WILDLEVEL(lchan[k]))
+ {
+ if (i == next->index)
+ {
+ prev = &next->left;
+ next = next->left;
+ }
+ else
+ {
+ /*
+ * The trie channel contains the wildcard '%' and the
+ * listen channel doesn't. So create a parent node with
+ * the listen channel on the left and the trie channel on
+ * the right.
+ */
+ TrieNode *child;
+ TrieNode *parent;
+
+ child = palloc(sizeof(TrieNode));
+ child->channel = lchan;
+ child->index = n;
+ child->length = l;
+ child->left = NULL;
+ child->right = NULL;
+
+ parent = palloc(sizeof(TrieNode));
+ parent->channel = next->channel;
+ parent->index = i;
+ parent->length = next->length;
+ parent->left = child;
+ parent->right = next;
+
+ *prev = parent;
+
+ break;
+ }
+ }
+ else if (!MATCH_OP_WILDLEVEL(next->channel[k]) &&
+ MATCH_OP_WILDLEVEL(lchan[k]))
+ {
+ /*
+ * The listen channel contains the wildcard '%' and the trie
+ * channel doesn't. So create a parent node with the trie
+ * channel on the left and the listen channel on the right.
+ */
+ TrieNode *child;
+ TrieNode *parent;
+
+ child = palloc(sizeof(TrieNode));
+ child->channel = lchan;
+ child->index = n;
+ child->length = l;
+ child->left = NULL;
+ child->right = NULL;
+
+ parent = palloc(sizeof(TrieNode));
+ parent->channel = lchan;
+ parent->index = i;
+ parent->length = l;
+ parent->left = next;
+ parent->right = child;
+
+ *prev = parent;
+
+ break;
+ }
+ else if (MATCH_OP_WILDLEVEL(next->channel[k]) &&
+ MATCH_OP_WILDLEVEL(lchan[k]))
+ {
+ if (i == next->index)
+ {
+ prev = &next->right;
+ next = next->right;
+ }
+ i += CHAR_BIT;
+ k++;
+ }
+ else
+ {
+ /*
+ * Find the index of the least significant bit on which the
+ * listen channel differs from the trie channel
+ */
+ x = lchan[k] ^ next->channel[k];
+ i = k * CHAR_BIT + MATCH_CTZC(x);
+ if (i < next->index)
+ {
+ if (x != 0)
+ {
+ /*
+ * Create a parent node with the index of the least
+ * significant bit on which the listen channel differs
+ * from the trie channel. If the least significant bit
+ * of the listen channel equals 0 then locate the
+ * listen channel on the left and the trie channel on
+ * the right. In the other case locate the channels in
+ * the reverse order.
+ */
+ TrieNode *child;
+ TrieNode *parent;
+
+ child = palloc(sizeof(TrieNode));
+ child->channel = lchan;
+ child->index = n;
+ child->length = l;
+ child->left = NULL;
+ child->right = NULL;
+
+ parent = palloc(sizeof(TrieNode));
+ parent->channel = lchan;
+ parent->index = i;
+ parent->length = l;
+
+ k = i / CHAR_BIT;
+ r = i % CHAR_BIT;
+ if (((lchan[k] >> r) & 1) == 0)
+ {
+ parent->left = child;
+ parent->right = next;
+ }
+ else
+ {
+ parent->left = next;
+ parent->right = child;
+ }
+
+ *prev = parent;
+
+ break;
+ }
+
+ k++;
+ }
+ else
+ {
+ i = next->index;
+ k = i / CHAR_BIT;
+ if (i < n &&
+ MATCH_OP_NOWILD(next->channel[k]) &&
+ MATCH_OP_NOWILD(lchan[k]))
+ {
+ /*
+ * Find the bit of the listen channel on which the
+ * left child differs from the right child
+ */
+ r = i % CHAR_BIT;
+ if (((lchan[k] >> r) & 1) == 0)
+ {
+ prev = &next->left;
+ next = next->left;
+ }
+ else
+ {
+ prev = &next->right;
+ next = next->right;
+ }
+ }
+ }
+ }
+ }
+ }
+
+ MemoryContextSwitchTo(oldcontext);
+}
+
+/*
+ * Delete the matching trie
+ */
+static void
+DeleteMatchingTrie()
+{
+ if (matchingTrie == NULL)
+ return;
+
+ FreeMatchingTrieRecursively(matchingTrie);
+ matchingTrie = NULL;
+}
+
+/*
+ * Free the memory allocated to the matching trie
+ */
+static void
+FreeMatchingTrieRecursively(TrieNode * node)
+{
+ /* Since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ if (!node)
+ return;
+ if (node->left)
+ FreeMatchingTrieRecursively(node->left);
+ if (node->right)
+ FreeMatchingTrieRecursively(node->right);
+ pfree(node);
+}
+
+/*
+ * Match the notification channel against the binary trie of the listen
+ * channels. If during the search in the mathching trie the function doesn't
+ * encounter the wildcard '%' then the time complexity can be estimated as
+ * O(n) where n is the length of the notification channel. The function
+ * matches the notification channel using a usual search in the binary trie
+ * except for the following two cases:
+ * 1. If the function encounters the wildcard '%' then the function matches
+ * everything until the end of the level.
+ * 2. If the function encounters the wildcard '*' then a match is found.
+ */
+static bool
+IsTrieMatchingOnRecursively(const char *channel,
+ TrieNode * node,
+ size_t channelIndex,
+ size_t parentIndex)
+{
+ size_t i = channelIndex;
+ size_t j = parentIndex;
+ size_t l = strlen(channel) + 1;
+ size_t n = l * CHAR_BIT;
+ size_t k = 0;
+ size_t t = 0;
+ size_t r;
+ size_t d;
+ char x;
+ TrieNode *next = node;
+
+ /* Since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ if (!node)
+ return false;
+
+ while (i < n)
+ {
+ if (MATCH_OP_WILDRIGHT(next->channel[t]))
+ {
+ /*
+ * The trie channel contains the wildcard '*' which matches
+ * everything until the end of the notification channel. So a
+ * match is found, break the loop and return true.
+ */
+ i = n;
+ }
+ else if (MATCH_OP_WILDLEVEL(next->channel[t]))
+ {
+ if (j == next->index)
+ {
+ /*
+ * At first the function goes to the right as if there is a
+ * match then it is higher likely located on the right
+ */
+ if (IsTrieMatchingOnRecursively(channel, next->right, i, j))
+ i = n;
+ else
+ next = next->left;
+ }
+ else
+ {
+ /*
+ * The trie channel contains the wildcard '%' which matches
+ * everything until the end of the level
+ */
+ while (!MATCH_OP_LEVEL(channel[k]))
+ {
+ i += CHAR_BIT;
+ k++;
+ }
+ j += CHAR_BIT;
+ t++;
+ }
+ }
+ else
+ {
+ /*
+ * Find the index of the least significant bit on which the
+ * notification channel differs from the trie channel
+ */
+ d = i - j;
+ x = channel[k] ^ next->channel[t];
+ j = t * CHAR_BIT + MATCH_CTZC(x);
+ if (j < next->index)
+ {
+ if (x != 0)
+ {
+ /* No match is found, break the loop and return false */
+ break;
+ }
+
+ i = j + d;
+ k++;
+ t++;
+ }
+ else
+ {
+ j = next->index;
+ i = j + d;
+ k = i / CHAR_BIT;
+ t = j / CHAR_BIT;
+ if (i < n && MATCH_OP_NOWILD(next->channel[t]))
+ {
+ /*
+ * Find the bit of the notification channel on which the
+ * left child differs from the right child
+ */
+ r = i % CHAR_BIT;
+ if (((channel[k] >> r) & 1) == 0)
+ next = next->left;
+ else
+ next = next->right;
+ }
+ }
+ }
+ }
+
+ return i == n;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a043fd4c66..26cf7852c6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -679,6 +679,12 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_object_constructor_null_clause_opt
json_array_constructor_null_clause_opt
+%type <str> listen_channel
+ listen_channel_inner_levels
+ listen_channel_inner_level
+ listen_channel_outer_level
+%type <str> notify_channel
+
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -10979,7 +10985,7 @@ opt_instead:
*
*****************************************************************************/
-NotifyStmt: NOTIFY ColId notify_payload
+NotifyStmt: NOTIFY notify_channel notify_payload
{
NotifyStmt *n = makeNode(NotifyStmt);
@@ -10989,12 +10995,19 @@ NotifyStmt: NOTIFY ColId notify_payload
}
;
+notify_channel:
+ ColId
+ { $$ = $1; }
+ | notify_channel '.' ColId
+ { $$ = psprintf("%s.%s", $1, $3); }
+ ;
+
notify_payload:
',' Sconst { $$ = $2; }
| /*EMPTY*/ { $$ = NULL; }
;
-ListenStmt: LISTEN ColId
+ListenStmt: LISTEN listen_channel
{
ListenStmt *n = makeNode(ListenStmt);
@@ -11004,20 +11017,41 @@ ListenStmt: LISTEN ColId
;
UnlistenStmt:
- UNLISTEN ColId
+ UNLISTEN listen_channel
{
UnlistenStmt *n = makeNode(UnlistenStmt);
n->conditionname = $2;
$$ = (Node *) n;
}
- | UNLISTEN '*'
- {
- UnlistenStmt *n = makeNode(UnlistenStmt);
+ ;
- n->conditionname = NULL;
- $$ = (Node *) n;
- }
+listen_channel:
+ listen_channel_outer_level
+ { $$ = $1; }
+ | listen_channel_inner_levels '.' listen_channel_outer_level
+ { $$ = psprintf("%s.%s", $1, $3); }
+ ;
+
+listen_channel_inner_levels:
+ listen_channel_inner_level
+ { $$ = $1; }
+ | listen_channel_inner_levels '.' listen_channel_inner_level
+ { $$ = psprintf("%s.%s", $1, $3); }
+ ;
+
+listen_channel_inner_level:
+ '%' { $$ = "%"; }
+ | ColId { $$ = $1; }
+ | ColId '%' { $$ = psprintf("%s%%", $1); }
+ ;
+
+listen_channel_outer_level:
+ '*' { $$ = "*"; }
+ | '%' { $$ = "%"; }
+ | ColId { $$ = $1; }
+ | ColId '*' { $$ = psprintf("%s*", $1); }
+ | ColId '%' { $$ = psprintf("%s%%", $1); }
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index fa66b8017e..a2ff0059b5 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -830,10 +830,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
UnlistenStmt *stmt = (UnlistenStmt *) parsetree;
CheckRestrictedOperation("UNLISTEN");
- if (stmt->conditionname)
- Async_Unlisten(stmt->conditionname);
- else
- Async_UnlistenAll();
+ Async_Unlisten(stmt->conditionname);
}
break;
diff --git a/src/test/isolation/expected/async-notify.out b/src/test/isolation/expected/async-notify.out
index 556e180589..c25586031e 100644
--- a/src/test/isolation/expected/async-notify.out
+++ b/src/test/isolation/expected/async-notify.out
@@ -1,4 +1,4 @@
-Parsed test spec with 3 sessions
+Parsed test spec with 4 sessions
starting permutation: listenc notify1 notify2 notify3 notifyf
step listenc: LISTEN c1; LISTEN c2;
@@ -125,3 +125,185 @@ nonzero
t
(1 row)
+
+starting permutation: mnotify
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+
+starting permutation: mlisten1 mnotify
+step mlisten1: LISTEN a;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+
+starting permutation: mlisten2 mnotify
+step mlisten2: LISTEN %;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+matching: NOTIFY "bc" with payload "" from matching
+matching: NOTIFY "ab" with payload "" from matching
+
+starting permutation: mlisten3 mnotify
+step mlisten3: LISTEN ab%;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab" with payload "" from matching
+
+starting permutation: mlisten4 mnotify
+step mlisten4: LISTEN *;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+matching: NOTIFY "bc" with payload "" from matching
+matching: NOTIFY "ab" with payload "" from matching
+matching: NOTIFY "cd.efg.ijk" with payload "" from matching
+matching: NOTIFY "ab.ef" with payload "" from matching
+matching: NOTIFY "abcd.eg" with payload "" from matching
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+matching: NOTIFY "abc.efg.ijk" with payload "" from matching
+matching: NOTIFY "ab.ef.ijk" with payload "" from matching
+matching: NOTIFY "ab.ee.l" with payload "" from matching
+
+starting permutation: mlisten5 mnotify
+step mlisten5: LISTEN cd*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "cd.efg.ijk" with payload "" from matching
+
+starting permutation: mlisten6 mnotify
+step mlisten6: LISTEN ab.ef;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab.ef" with payload "" from matching
+
+starting permutation: mlisten7 mnotify
+step mlisten7: LISTEN ab%.eg;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "abcd.eg" with payload "" from matching
+
+starting permutation: mlisten8 mnotify
+step mlisten8: LISTEN %.eh*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+
+starting permutation: mlisten9 mnotify
+step mlisten9: LISTEN ab.ef%.*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab.ef.ijk" with payload "" from matching
+
+starting permutation: mlisten10 mnotify
+step mlisten10: LISTEN ab.ee.l;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab.ee.l" with payload "" from matching
+
+starting permutation: mlisten1 mlisten8 mnotify
+step mlisten1: LISTEN a;
+step mlisten8: LISTEN %.eh*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+
+starting permutation: mlisten6 mlisten7 mnotify
+step mlisten6: LISTEN ab.ef;
+step mlisten7: LISTEN ab%.eg;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab.ef" with payload "" from matching
+matching: NOTIFY "abcd.eg" with payload "" from matching
+
+starting permutation: mlisten6 mlisten7 mlisten8 mnotify
+step mlisten6: LISTEN ab.ef;
+step mlisten7: LISTEN ab%.eg;
+step mlisten8: LISTEN %.eh*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab.ef" with payload "" from matching
+matching: NOTIFY "abcd.eg" with payload "" from matching
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+
+starting permutation: mlisten1 mlisten6 mlisten7 mnotify
+step mlisten1: LISTEN a;
+step mlisten6: LISTEN ab.ef;
+step mlisten7: LISTEN ab%.eg;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+matching: NOTIFY "ab.ef" with payload "" from matching
+matching: NOTIFY "abcd.eg" with payload "" from matching
+
+starting permutation: mlisten1 mlisten3 mlisten5 mlisten6 mlisten8 mlisten9 mnotify
+step mlisten1: LISTEN a;
+step mlisten3: LISTEN ab%;
+step mlisten5: LISTEN cd*;
+step mlisten6: LISTEN ab.ef;
+step mlisten8: LISTEN %.eh*;
+step mlisten9: LISTEN ab.ef%.*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+matching: NOTIFY "ab" with payload "" from matching
+matching: NOTIFY "cd.efg.ijk" with payload "" from matching
+matching: NOTIFY "ab.ef" with payload "" from matching
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+matching: NOTIFY "ab.ef.ijk" with payload "" from matching
+
+starting permutation: mlisten2 mlisten4 mlisten7 mlisten8 mlisten9 mlisten10 mnotify
+step mlisten2: LISTEN %;
+step mlisten4: LISTEN *;
+step mlisten7: LISTEN ab%.eg;
+step mlisten8: LISTEN %.eh*;
+step mlisten9: LISTEN ab.ef%.*;
+step mlisten10: LISTEN ab.ee.l;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "a" with payload "" from matching
+matching: NOTIFY "bc" with payload "" from matching
+matching: NOTIFY "ab" with payload "" from matching
+matching: NOTIFY "cd.efg.ijk" with payload "" from matching
+matching: NOTIFY "ab.ef" with payload "" from matching
+matching: NOTIFY "abcd.eg" with payload "" from matching
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+matching: NOTIFY "abc.efg.ijk" with payload "" from matching
+matching: NOTIFY "ab.ef.ijk" with payload "" from matching
+matching: NOTIFY "ab.ee.l" with payload "" from matching
+
+starting permutation: mlisten1 munlisten1 mnotify
+step mlisten1: LISTEN a;
+step munlisten1: UNLISTEN a%;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+
+starting permutation: mlisten3 munlisten2 mnotify
+step mlisten3: LISTEN ab%;
+step munlisten2: UNLISTEN abc%;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab" with payload "" from matching
+
+starting permutation: mlisten7 munlisten3 mnotify
+step mlisten7: LISTEN ab%.eg;
+step munlisten3: UNLISTEN %.e*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+
+starting permutation: mlisten5 munlisten4 mnotify
+step mlisten5: LISTEN cd*;
+step munlisten4: UNLISTEN cd*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+
+starting permutation: mlisten9 munlisten5 mnotify
+step mlisten9: LISTEN ab.ef%.*;
+step munlisten5: UNLISTEN ab.%;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "ab.ef.ijk" with payload "" from matching
+
+starting permutation: mlisten7 mlisten8 munlisten5 mnotify
+step mlisten7: LISTEN ab%.eg;
+step mlisten8: LISTEN %.eh*;
+step munlisten5: UNLISTEN ab.%;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "abcd.eg" with payload "" from matching
+matching: NOTIFY "abcd.ehfg" with payload "" from matching
+
+starting permutation: mlisten5 mlisten6 munlisten2 munlisten3 mnotify
+step mlisten5: LISTEN cd*;
+step mlisten6: LISTEN ab.ef;
+step munlisten2: UNLISTEN abc%;
+step munlisten3: UNLISTEN %.e*;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
+matching: NOTIFY "cd.efg.ijk" with payload "" from matching
+
+starting permutation: mlisten1 mlisten3 mlisten5 mlisten6 mlisten8 munlisten6 mnotify
+step mlisten1: LISTEN a;
+step mlisten3: LISTEN ab%;
+step mlisten5: LISTEN cd*;
+step mlisten6: LISTEN ab.ef;
+step mlisten8: LISTEN %.eh*;
+step munlisten6: UNLISTEN *;
+step mnotify: NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l;
diff --git a/src/test/isolation/specs/async-notify.spec b/src/test/isolation/specs/async-notify.spec
index 0b8cfd9108..b47fa1b0c5 100644
--- a/src/test/isolation/specs/async-notify.spec
+++ b/src/test/isolation/specs/async-notify.spec
@@ -53,6 +53,28 @@ step l2begin { BEGIN; }
step l2commit { COMMIT; }
step l2stop { UNLISTEN *; }
+# A separate session to check wildcards in LISTEN and UNLISTEN commands
+
+session matching
+step mlisten1 { LISTEN a; }
+step mlisten2 { LISTEN %; }
+step mlisten3 { LISTEN ab%; }
+step mlisten4 { LISTEN *; }
+step mlisten5 { LISTEN cd*; }
+step mlisten6 { LISTEN ab.ef; }
+step mlisten7 { LISTEN ab%.eg; }
+step mlisten8 { LISTEN %.eh*; }
+step mlisten9 { LISTEN ab.ef%.*; }
+step mlisten10 { LISTEN ab.ee.l; }
+step munlisten1 { UNLISTEN a%; }
+step munlisten2 { UNLISTEN abc%; }
+step munlisten3 { UNLISTEN %.e*; }
+step munlisten4 { UNLISTEN cd*; }
+step munlisten5 { UNLISTEN ab.%; }
+step munlisten6 { UNLISTEN *; }
+step mnotify { NOTIFY a; NOTIFY bc; NOTIFY ab; NOTIFY cd.efg.ijk; NOTIFY ab.ef; NOTIFY abcd.eg; NOTIFY abcd.ehfg; NOTIFY abc.efg.ijk; NOTIFY ab.ef.ijk; NOTIFY ab.ee.l; }
+teardown { UNLISTEN *; }
+
# Trivial cases.
permutation listenc notify1 notify2 notify3 notifyf
@@ -82,3 +104,31 @@ permutation l2listen l2begin notify1 lbegins llisten lcommit l2commit l2stop
# Hence, this should be the last test in this script.
permutation llisten lbegin usage bignotify usage
+
+# Check wildcards in LISTEN and UNLISTEN commands
+
+permutation mnotify
+permutation mlisten1 mnotify
+permutation mlisten2 mnotify
+permutation mlisten3 mnotify
+permutation mlisten4 mnotify
+permutation mlisten5 mnotify
+permutation mlisten6 mnotify
+permutation mlisten7 mnotify
+permutation mlisten8 mnotify
+permutation mlisten9 mnotify
+permutation mlisten10 mnotify
+permutation mlisten1 mlisten8 mnotify
+permutation mlisten6 mlisten7 mnotify
+permutation mlisten6 mlisten7 mlisten8 mnotify
+permutation mlisten1 mlisten6 mlisten7 mnotify
+permutation mlisten1 mlisten3 mlisten5 mlisten6 mlisten8 mlisten9 mnotify
+permutation mlisten2 mlisten4 mlisten7 mlisten8 mlisten9 mlisten10 mnotify
+permutation mlisten1 munlisten1 mnotify
+permutation mlisten3 munlisten2 mnotify
+permutation mlisten7 munlisten3 mnotify
+permutation mlisten5 munlisten4 mnotify
+permutation mlisten9 munlisten5 mnotify
+permutation mlisten7 mlisten8 munlisten5 mnotify
+permutation mlisten5 mlisten6 munlisten2 munlisten3 mnotify
+permutation mlisten1 mlisten3 mlisten5 mlisten6 mlisten8 munlisten6 mnotify
diff --git a/src/test/regress/expected/async.out b/src/test/regress/expected/async.out
index 19cbe38e63..0a2af71381 100644
--- a/src/test/regress/expected/async.out
+++ b/src/test/regress/expected/async.out
@@ -27,11 +27,118 @@ SELECT pg_notify(NULL,'sample message1');
ERROR: channel name cannot be empty
SELECT pg_notify('notify_async_channel_name_too_long______________________________','sample_message1');
ERROR: channel name too long
---Should work. Valid NOTIFY/LISTEN/UNLISTEN commands
-NOTIFY notify_async2;
-LISTEN notify_async2;
-UNLISTEN notify_async2;
+-- Should work. Valid NOTIFY commands, multiple levels
+NOTIFY a;
+NOTIFY a.b;
+NOTIFY a.b.c;
+-- Should fail. Invalid NOTIFY commands, empty levels
+NOTIFY a.b.;
+ERROR: syntax error at or near ";"
+LINE 1: NOTIFY a.b.;
+ ^
+NOTIFY .b.c;
+ERROR: syntax error at or near "."
+LINE 1: NOTIFY .b.c;
+ ^
+NOTIFY a..c;
+ERROR: syntax error at or near ".."
+LINE 1: NOTIFY a..c;
+ ^
+-- Should work. Valid LISTEN/UNLISTEN commands, multiple levels and wildcards
+LISTEN a;
+LISTEN %;
+LISTEN a%;
+LISTEN *;
+LISTEN a*;
+LISTEN a.b;
+LISTEN %.b;
+LISTEN %.b%;
+LISTEN a.*;
+LISTEN a.b*;
+LISTEN a.b.c;
+LISTEN a.b%.a*;
+UNLISTEN a;
+UNLISTEN %;
+UNLISTEN a%;
UNLISTEN *;
+UNLISTEN a*;
+UNLISTEN a.b;
+UNLISTEN %.b;
+UNLISTEN %.b%;
+UNLISTEN a.*;
+UNLISTEN a.b*;
+UNLISTEN a.b.c;
+UNLISTEN a.b%.a*;
+UNLISTEN *;
+-- Should fail. Invalid LISTEN/UNLISTEN commands, empty levels
+LISTEN a.b%.;
+ERROR: syntax error at or near ";"
+LINE 1: LISTEN a.b%.;
+ ^
+LISTEN .b%.c*;
+ERROR: syntax error at or near "."
+LINE 1: LISTEN .b%.c*;
+ ^
+LISTEN a..%;
+ERROR: syntax error at or near ".."
+LINE 1: LISTEN a..%;
+ ^
+UNLISTEN a.b%.;
+ERROR: syntax error at or near ";"
+LINE 1: UNLISTEN a.b%.;
+ ^
+UNLISTEN .b%.c*;
+ERROR: syntax error at or near "."
+LINE 1: UNLISTEN .b%.c*;
+ ^
+UNLISTEN a..%;
+ERROR: syntax error at or near ".."
+LINE 1: UNLISTEN a..%;
+ ^
+-- Should fail. Invalid LISTEN/UNLISTEN commands, the wildcard '%' can only be
+-- located at the end of a level
+LISTEN %a;
+ERROR: syntax error at or near "a"
+LINE 1: LISTEN %a;
+ ^
+LISTEN %*;
+ERROR: syntax error at or near "%*"
+LINE 1: LISTEN %*;
+ ^
+UNLISTEN %a;
+ERROR: syntax error at or near "a"
+LINE 1: UNLISTEN %a;
+ ^
+UNLISTEN %*;
+ERROR: syntax error at or near "%*"
+LINE 1: UNLISTEN %*;
+ ^
+-- Should fail. Invalid LISTEN/UNLISTEN commands, the wildcard '*' can only be
+-- located at the end of a channel name
+LISTEN *.;
+ERROR: syntax error at or near "."
+LINE 1: LISTEN *.;
+ ^
+LISTEN *a;
+ERROR: syntax error at or near "a"
+LINE 1: LISTEN *a;
+ ^
+LISTEN *%;
+ERROR: syntax error at or near "*%"
+LINE 1: LISTEN *%;
+ ^
+UNLISTEN *.;
+ERROR: syntax error at or near "."
+LINE 1: UNLISTEN *.;
+ ^
+UNLISTEN *a;
+ERROR: syntax error at or near "a"
+LINE 1: UNLISTEN *a;
+ ^
+UNLISTEN *%;
+ERROR: syntax error at or near "*%"
+LINE 1: UNLISTEN *%;
+ ^
-- Should return zero while there are no pending notifications.
-- src/test/isolation/specs/async-notify.spec tests for actual usage.
SELECT pg_notification_queue_usage();
diff --git a/src/test/regress/sql/async.sql b/src/test/regress/sql/async.sql
index 40f6e01538..5f9fe5a0ab 100644
--- a/src/test/regress/sql/async.sql
+++ b/src/test/regress/sql/async.sql
@@ -12,12 +12,67 @@ SELECT pg_notify('','sample message1');
SELECT pg_notify(NULL,'sample message1');
SELECT pg_notify('notify_async_channel_name_too_long______________________________','sample_message1');
---Should work. Valid NOTIFY/LISTEN/UNLISTEN commands
-NOTIFY notify_async2;
-LISTEN notify_async2;
-UNLISTEN notify_async2;
+-- Should work. Valid NOTIFY commands, multiple levels
+NOTIFY a;
+NOTIFY a.b;
+NOTIFY a.b.c;
+
+-- Should fail. Invalid NOTIFY commands, empty levels
+NOTIFY a.b.;
+NOTIFY .b.c;
+NOTIFY a..c;
+
+-- Should work. Valid LISTEN/UNLISTEN commands, multiple levels and wildcards
+LISTEN a;
+LISTEN %;
+LISTEN a%;
+LISTEN *;
+LISTEN a*;
+LISTEN a.b;
+LISTEN %.b;
+LISTEN %.b%;
+LISTEN a.*;
+LISTEN a.b*;
+LISTEN a.b.c;
+LISTEN a.b%.a*;
+UNLISTEN a;
+UNLISTEN %;
+UNLISTEN a%;
+UNLISTEN *;
+UNLISTEN a*;
+UNLISTEN a.b;
+UNLISTEN %.b;
+UNLISTEN %.b%;
+UNLISTEN a.*;
+UNLISTEN a.b*;
+UNLISTEN a.b.c;
+UNLISTEN a.b%.a*;
UNLISTEN *;
+-- Should fail. Invalid LISTEN/UNLISTEN commands, empty levels
+LISTEN a.b%.;
+LISTEN .b%.c*;
+LISTEN a..%;
+UNLISTEN a.b%.;
+UNLISTEN .b%.c*;
+UNLISTEN a..%;
+
+-- Should fail. Invalid LISTEN/UNLISTEN commands, the wildcard '%' can only be
+-- located at the end of a level
+LISTEN %a;
+LISTEN %*;
+UNLISTEN %a;
+UNLISTEN %*;
+
+-- Should fail. Invalid LISTEN/UNLISTEN commands, the wildcard '*' can only be
+-- located at the end of a channel name
+LISTEN *.;
+LISTEN *a;
+LISTEN *%;
+UNLISTEN *.;
+UNLISTEN *a;
+UNLISTEN *%;
+
-- Should return zero while there are no pending notifications.
-- src/test/isolation/specs/async-notify.spec tests for actual usage.
SELECT pg_notification_queue_usage();
--
2.25.1
