+1 ... and a patch (only adding ALL keyword, no hash table implemented yet).
On Sat, Feb 6, 2016 at 2:35 PM, Brendan Jurd <dire...@gmail.com> wrote: > On Sat, 6 Feb 2016 at 12:50 Tom Lane <t...@sss.pgh.pa.us> wrote: >> >> Robert Haas <robertmh...@gmail.com> writes: >> > I agree with what Merlin said about this: >> > >> > http://www.postgresql.org/message-id/CAHyXU0yoHe8Qc=yc10ahu1nfia1tbhsg+35ds-oeueuapo7...@mail.gmail.com >> >> Yeah, I agree that a GUC for this is quite unappetizing. > > > How would you feel about a variant for calling NOTIFY? > > The SQL syntax could be something like "NOTIFY [ALL] channel, payload" where > the ALL means "just send the notification already, nobody cares whether > there's an identical one in the queue". > > Likewise we could introduce a three-argument form of pg_notify(text, text, > bool) where the final argument is whether you are interested in removing > duplicates. > > Optimising the remove-duplicates path is still probably a worthwhile > endeavour, but if the user really doesn't care at all about duplication, it > seems silly to force them to pay any performance price for a behaviour they > didn't want, no? > > Cheers, > BJ
diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml index 4dd5608..c148859 100644 --- a/doc/src/sgml/ref/notify.sgml +++ b/doc/src/sgml/ref/notify.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -NOTIFY <replaceable class="PARAMETER">channel</replaceable> [ , <replaceable class="PARAMETER">payload</replaceable> ] +NOTIFY [ ALL | DISTINCT ] <replaceable class="PARAMETER">channel</replaceable> [ , <replaceable class="PARAMETER">payload</replaceable> ] </synopsis> </refsynopsisdiv> @@ -105,6 +105,8 @@ NOTIFY <replaceable class="PARAMETER">channel</replaceable> [ , <replaceable cla transaction get delivered in the order they were sent. It is also guaranteed that messages from different transactions are delivered in the order in which the transactions committed. + If <literal>ALL</> is specified (contrary to <literal>DISTINCT</>, the + default), the server will deliver all notifications, including duplicates. </para> <para> @@ -184,11 +186,14 @@ NOTIFY <replaceable class="PARAMETER">channel</replaceable> [ , <replaceable cla <para> To send a notification you can also use the function - <literal><function>pg_notify</function>(<type>text</type>, - <type>text</type>)</literal>. The function takes the channel name as the - first argument and the payload as the second. The function is much easier - to use than the <command>NOTIFY</command> command if you need to work with - non-constant channel names and payloads. + <literal><function>pg_notify(<parameter>channel</> <type>text</>, + <parameter>payload</parameter> <type>text</> <optional>, + <parameter>use_all</> <type>boolean</></optional>)</function></literal>. + The function takes the channel name as the first argument and the payload + as the second. The third argument, <literal>false</> by default, represents + the <literal>ALL</> keyword. The function is much easier to use than the + <command>NOTIFY</command> command if you need to work with non-constant + channel names and payloads. </para> </refsect2> </refsect1> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 923fe58..9df5301 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -965,3 +965,10 @@ RETURNS jsonb LANGUAGE INTERNAL STRICT IMMUTABLE AS 'jsonb_set'; + +CREATE OR REPLACE FUNCTION + pg_notify(channel text, payload text, use_all boolean DEFAULT false) +RETURNS void +LANGUAGE INTERNAL +VOLATILE +AS 'pg_notify'; diff --git a/src/backend/commands/async.c b/src/backend/commands/async.c index c39ac3a..d374a00 100644 --- a/src/backend/commands/async.c +++ b/src/backend/commands/async.c @@ -510,6 +510,7 @@ pg_notify(PG_FUNCTION_ARGS) { const char *channel; const char *payload; + bool use_all; if (PG_ARGISNULL(0)) channel = ""; @@ -521,10 +522,12 @@ pg_notify(PG_FUNCTION_ARGS) else payload = text_to_cstring(PG_GETARG_TEXT_PP(1)); + use_all = PG_GETARG_BOOL(2); + /* For NOTIFY as a statement, this is checked in ProcessUtility */ PreventCommandDuringRecovery("NOTIFY"); - Async_Notify(channel, payload); + Async_Notify(channel, payload, use_all); PG_RETURN_VOID(); } @@ -540,7 +543,7 @@ pg_notify(PG_FUNCTION_ARGS) * ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ */ void -Async_Notify(const char *channel, const char *payload) +Async_Notify(const char *channel, const char *payload, bool use_all) { Notification *n; MemoryContext oldcontext; @@ -570,9 +573,10 @@ Async_Notify(const char *channel, const char *payload) errmsg("payload string too long"))); } - /* no point in making duplicate entries in the list ... */ - if (AsyncExistsPendingNotify(channel, payload)) - return; + if (!use_all) + /* remove duplicate entries in the list */ + if (AsyncExistsPendingNotify(channel, payload)) + return; /* * The notification list needs to live until end of transaction, so store diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index b307b48..7203f4a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -8528,11 +8528,12 @@ DropRuleStmt: * *****************************************************************************/ -NotifyStmt: NOTIFY ColId notify_payload +NotifyStmt: NOTIFY all_or_distinct ColId notify_payload { NotifyStmt *n = makeNode(NotifyStmt); - n->conditionname = $2; - n->payload = $3; + n->use_all = $2; + n->conditionname = $3; + n->payload = $4; $$ = (Node *)n; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 045f7f0..0e50561 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -599,7 +599,7 @@ standard_ProcessUtility(Node *parsetree, NotifyStmt *stmt = (NotifyStmt *) parsetree; PreventCommandDuringRecovery("NOTIFY"); - Async_Notify(stmt->conditionname, stmt->payload); + Async_Notify(stmt->conditionname, stmt->payload, stmt->use_all); } break; diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index c6b4916..14b1ed0 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4005,7 +4005,7 @@ DESCR("trigger description with pretty-print option"); /* asynchronous notifications */ DATA(insert OID = 3035 ( pg_listening_channels PGNSP PGUID 12 1 10 0 0 f f f f t t s r 0 0 25 "" _null_ _null_ _null_ _null_ _null_ pg_listening_channels _null_ _null_ _null_ )); DESCR("get the channels that the current backend listens to"); -DATA(insert OID = 3036 ( pg_notify PGNSP PGUID 12 1 0 0 0 f f f f f f v r 2 0 2278 "25 25" _null_ _null_ _null_ _null_ _null_ pg_notify _null_ _null_ _null_ )); +DATA(insert OID = 3036 ( pg_notify PGNSP PGUID 12 1 0 0 0 f f f f f f v r 3 0 2278 "25 25 16" _null_ _null_ _null_ _null_ _null_ pg_notify _null_ _null_ _null_ )); DESCR("send a notification event"); DATA(insert OID = 3296 ( pg_notification_queue_usage PGNSP PGUID 12 1 0 0 0 f f f f t f v s 0 0 701 "" _null_ _null_ _null_ _null_ _null_ pg_notification_queue_usage _null_ _null_ _null_ )); DESCR("get the fraction of the asynchronous notification queue currently in use"); diff --git a/src/include/commands/async.h b/src/include/commands/async.h index b4c13fa..3a2c1c2 100644 --- a/src/include/commands/async.h +++ b/src/include/commands/async.h @@ -29,7 +29,7 @@ extern Size AsyncShmemSize(void); extern void AsyncShmemInit(void); /* notify-related SQL statements */ -extern void Async_Notify(const char *channel, const char *payload); +extern void Async_Notify(const char *channel, const char *payload, bool use_all); extern void Async_Listen(const char *channel); extern void Async_Unlisten(const char *channel); extern void Async_UnlistenAll(void); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 2fd0629..7f1f01d 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2590,6 +2590,7 @@ typedef struct NotifyStmt NodeTag type; char *conditionname; /* condition name to notify */ char *payload; /* the payload string, or NULL if none */ + bool use_all; /* ALL option */ } NotifyStmt; /* ---------------------- diff --git a/src/test/regress/expected/async.out b/src/test/regress/expected/async.out index 19cbe38..c650b90 100644 --- a/src/test/regress/expected/async.out +++ b/src/test/regress/expected/async.out @@ -8,6 +8,18 @@ SELECT pg_notify('notify_async1','sample message1'); (1 row) +SELECT pg_notify('notify_async1','sample message1',false); + pg_notify +----------- + +(1 row) + +SELECT pg_notify('notify_async1','sample_message1',true); + pg_notify +----------- + +(1 row) + SELECT pg_notify('notify_async1',''); pg_notify ----------- @@ -29,6 +41,8 @@ SELECT pg_notify('notify_async_channel_name_too_long____________________________ ERROR: channel name too long --Should work. Valid NOTIFY/LISTEN/UNLISTEN commands NOTIFY notify_async2; +NOTIFY DISTINCT notify_async2; +NOTIFY ALL notify_async2; LISTEN notify_async2; UNLISTEN notify_async2; UNLISTEN *; diff --git a/src/test/regress/sql/async.sql b/src/test/regress/sql/async.sql index 40f6e01..6e53b86 100644 --- a/src/test/regress/sql/async.sql +++ b/src/test/regress/sql/async.sql @@ -4,6 +4,8 @@ --Should work. Send a valid message via a valid channel name SELECT pg_notify('notify_async1','sample message1'); +SELECT pg_notify('notify_async1','sample message1',false); +SELECT pg_notify('notify_async1','sample_message1',true); SELECT pg_notify('notify_async1',''); SELECT pg_notify('notify_async1',NULL); @@ -14,6 +16,8 @@ SELECT pg_notify('notify_async_channel_name_too_long____________________________ --Should work. Valid NOTIFY/LISTEN/UNLISTEN commands NOTIFY notify_async2; +NOTIFY DISTINCT notify_async2; +NOTIFY ALL notify_async2; LISTEN notify_async2; UNLISTEN notify_async2; UNLISTEN *;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers