Re: proposal: make NOTIFY list de-duplication optional - Mailing list pgsql-hackers

From Filip Rembiałkowski
Subject Re: proposal: make NOTIFY list de-duplication optional
Date
Msg-id CAP_rww=ezkD4a-D_tTV2of92ZuC=DnPEFwC4vu8z4uViMnbw9g@mail.gmail.com
Whole thread Raw
In response to Re: proposal: make NOTIFY list de-duplication optional  (Catalin Iacob <iacobcatalin@gmail.com>)
Responses Re: proposal: make NOTIFY list de-duplication optional
Re: proposal: make NOTIFY list de-duplication optional
List pgsql-hackers
On Fri, Feb 19, 2016 at 10:09 PM, Catalin Iacob <iacobcatalin@gmail.com> wrote:
On 2/9/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> FWIW, I think it would be a good thing if the NOTIFY statement syntax were
> not remarkably different from the syntax used in the pg_notify() function
> call.  To do otherwise would certainly be confusing.  So on the whole
> I'd go with the "NOTIFY channel [ , payload [ , mode ] ]" option.

I'm quite interested in getting this addressed in time for 9.6 as I'll
be using NOTIFY extensively in a project and I agree with Craig that
the deduplication is frustrating both because you sometimes want every
event and because it can apparently cause O(n^2) behaviour (which I
didn't know before this thread). If another use case for suppressing
deduplication is needed, consider publishing events like "inserted
tuple", "deleted tuple" from triggers and a transaction that does
"insert, delete, insert" which the client then sees as "insert,
delete, oops nothing else".

Tom's proposal allows for more flexible modes than just the ALL and
DISTINCT keywords and accommodates the concern that DISTINCT will lead
to bug reports about not really being distinct due to savepoints.

Android has a similar thing for push notifications to mobile devices
which they call collapse:
https://developers.google.com/cloud-messaging/concept-options, search
for collapse_key.

So I propose NOTIFY channel [ , payload [ , collapse_mode ] ] with
collapse mode being:

* 'never'
  ** Filip's proposed behaviour for the ALL option
  ** if specified, every notification is queued regardless what's in the queue

* 'maybe'
  ** vague word allowing for flexibility in what the server decides to do
  ** current behaviour
  ** improves performance for big transactions if a row trigger
creates the same payload over and over one after the other due to the
current optimization of checking the tail of the list
  ** has performance problems O(n^2) for big transactions with
different payloads
      *** the performance problems can be addressed by a different
patch which uses a hash table, or decides to collapse less
aggressively (Tom's check last 100 idea), or whatever else
      *** in the meantime the 'never' mode acts as a good workaround

In the future we might support an 'always' collapse_mode which would
really be always, including across savepoints. Or an
'only_inside_savepoints' which guarantees the current behaviour.

Filip, do you agree with Tom's proposal? Do you plan to rework the
patch on these lines? If you are I'll try to review it, if not I could
give it a shot as I'm interested in having this in 9.6.

I see that Tom's remarks give more flexibility, and your refinement makes sense.

I was stuck because both syntaxes have their ugliness. NOTIFY allows the payload to be NULL:
NOTIFY chan01;

How would this look like in "never" mode?
NOTIFY chan01, NULL, 'never'; -- seems very cryptic.



pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Spurious standby query cancellations
Next
From: Craig Ringer
Date:
Subject: Re: JDBC behaviour