Thread: Cheapest way to poll for notifications?
Hi As Pg doesn't presently support client push for notifications arising from LISTEN/NOTIFY, I'm wondering if anybody here has done any research into the cheapest statement to issue to check for such notifications. First: is it worth caring? Or is a `SELECT 1;' every few seconds from a small (50-ish) number of clients unlikely to have a detectable effect on load? Does it have any significant costs (considering that each statement starts its own transaction) ? Is it worth issuing: BEGIN ISOLATION LEVEL READ COMMITTED, READ ONLY; SELECT 1; ROLLBACK; rather than just `SELECT 1' ? I'll be doing some testing on all of this, of course, but I thought I'd ask in case others have looked into this already. I didn't see anything much in the archives. One thing I've noticed is that an invalid statement that fails to parse still produces the notifications, though it avoids planning and query execution. However, it'll also fill the error log with garbage, so it's not exactly desirable. The error paths are probably more expensive than just running the SELECT anyway. I wonder if it'd be useful to have a utility statement (perhaps 'LISTEN' without arguments?) that was essentially a no-op that _only_ checked notifications. It'd make it easier to see why clients were issuing apparently random statements, and could possibly be also excluded from statement logging even when other things were logged. Would there be any support for the idea of something like that? It might be project #2 for me once I get around to posting the array_reverse function, if so. Also: Is there any practical way Pg can ever support server-to-client push for notifications? I assume the reasons why it doesn't at present are to do with the protocol and with deadlock/concurrency issues at the wire level ... is that so? -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
> As Pg doesn't presently support client push for notifications arising
> from LISTEN/NOTIFY, I'm wondering if anybody here has done any research
> into the cheapest statement to issue to check for such notifications.
The backend certainly will push the notification. Maybe you just have
a client-side-software issue?
regards, tom lane
On Thu, Dec 10, 2009 at 8:23 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Hi > > As Pg doesn't presently support client push for notifications arising from > LISTEN/NOTIFY, I'm wondering if anybody here has done any research into the > cheapest statement to issue to check for such notifications. notifications are sent to the client as soon as they are generated and the socket is not otherwise being used. psql just prints the notifcation message following a query because that happens to be a convenient place to do it. The only polling that has to be done (if any) is 100% client side. There is an excellent example (in C) in the documentation showing a method of waiting on the connection socket for notification events when the connection is not in use by the client. merlin
Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
>> As Pg doesn't presently support client push for notifications arising
>> from LISTEN/NOTIFY, I'm wondering if anybody here has done any research
>> into the cheapest statement to issue to check for such notifications.
>
> The backend certainly will push the notification. Maybe you just have
> a client-side-software issue?
Testing with two plain old psql sessions to an 8.4.1 DB on linux (Ubuntu
9.10) here, I don't see the asynchronous notification until I send some
other command to the database from the client. It's the same over a
local UNIX socket or a loopback TCP/IP connection (with SSL).
Neither listener nor notifier are in a transaction, though it doesn't
seem to make any difference if I "BEGIN; NOTIFY test; COMMIT;" instead.
I only waited a few minutes after sending NOTIFY to see if psql would
notice, but if it's server-pushed it should be immediate, right?
Is this a psql limitation rather than a backend one? I know the JDBC
driver has such a limitation, and in fact the psql man page suggests
that it does too:
"
Whenever a command is executed, psql also polls for asynchronous noti‐
fication events generated by LISTEN [listen(7)] and NOTIFY [notify(7)].
"
... but if that is the case, is there any client software that _does_
support truly asynchronous receipt of notifications?
=====(SESSION 1, psql 8.4.1, local socket)====
test=> LISTEN x;
LISTEN
test=> SELECT current_timestamp;
now
-------------------------------
2009-12-10 23:54:16.988355+08
(1 row)
test=> SELECT current_timestamp;
now
-------------------------------
2009-12-10 23:55:04.872358+08
(1 row)
Asynchronous notification "x" received from server process with PID 14623.
test=>
=====(SESSION 2, psql 8.4.1, local socket)====
test=> SELECT current_timestamp;
now
-------------------------------
2009-12-10 23:54:34.252394+08
(1 row)
test=> NOTIFY x;
NOTIFY
test=>
*Self* notifications ( where listener == notifier ) are issued
immediately, but a NOTIFY to another backend doesn't seem to get pushed.
--
Craig Ringer
Merlin Moncure wrote: > On Thu, Dec 10, 2009 at 8:23 AM, Craig Ringer > <craig@postnewspapers.com.au> wrote: >> Hi >> >> As Pg doesn't presently support client push for notifications arising from >> LISTEN/NOTIFY, I'm wondering if anybody here has done any research into the >> cheapest statement to issue to check for such notifications. > > notifications are sent to the client as soon as they are generated and > the socket is not otherwise being used. psql just prints the > notifcation message following a query because that happens to be a > convenient place to do it. The only polling that has to be done (if > any) is 100% client side. There is an excellent example (in C) in the > documentation showing a method of waiting on the connection socket for > notification events when the connection is not in use by the client. Aah, that makes sense. So psql is being sent (and buffering) the notification immediately, and could choose to report it promptly - say by calling recv(...) on its socket periodically using an alarm signal. Since it's not important for psql to report notifications immediately though, it only checks after statements. Right? Anyway, thanks for the explanation, it's appreciated. -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
> Tom Lane wrote:
>> The backend certainly will push the notification. Maybe you just have
>> a client-side-software issue?
> Testing with two plain old psql sessions to an 8.4.1 DB on linux (Ubuntu
> 9.10) here, I don't see the asynchronous notification until I send some
> other command to the database from the client. It's the same over a
> local UNIX socket or a loopback TCP/IP connection (with SSL).
psql is not too bright about that; when it's waiting for user input it
just waits. But I don't think it matters because nobody is going to
write an application that depends on this in psql anyway. If you are
writing something that uses libpq directly, it's certainly possible to
watch for incoming notifies along with whatever else your event loop is
watching for. (I had an application that did so a dozen years ago...)
regards, tom lane
> Right?
Right. The way I do it, very roughly:
Pqconnectdb(...)
PQexec( mDbConn, "listen notify" );
int dbsock = PQsocket( mDbConn );
mKq = kqueue();
struct kevent kev[1], kevs[1];
EV_SET( &kev[0], dbsock, EVFILT_READ, EV_ADD | EV_ENABLE, 0, 0, 0 );
kevent( mKq, kev, 1, NULL, 0, NULL );
while( true ) {
bool needsnotify = false;
int evtcnt = kevent( mKq, NULL, 0, kevs, 1, NULL );
if( evtcnt == 1 && kevs[i].filter == EVFILT_READ && kevs[i].ident ==
dbsock ) {
while( true ) {
PQconsumeInput( mDbConn );
PGnotify * notify = PQnotifies( mDbConn );
if( notify ) {
free( notify );
needsnotify = true;
}
else
break;
}
}
if( needsnotify )
// go off & query the db & handle updates here
}
Of course you could also use select to wait on the socket.
--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice
Scott, Tom, Merlin: Thanks for the comments and help. It's all sorted now - the origin of the confusion was some outdated information in the JDBC driver documentation. The question arose because I was originally looking at polling from JDBC (which I know I forgot to mention), where the docs state that: "A key limitation of the JDBC driver is that it cannot receive asynchronous notifications and must poll the backend to check if any notifications were issued." http://jdbc.postgresql.org/documentation/84/listennotify.html .... and show a `SELECT 1' being issued to push any notifications. I'd assumed that was a JDBC limitation until I tested with psql and found that it, too, required some kind of client-initiated communication to see NOTIFY events, at which point I began wondering if the backend pushed them at all rather than waiting for client interaction. Hence my question. Anyway, as pointed out, psql just doesn't bother polling for notifications because it's not important for psql, but it could if it needed to - the notifications are waiting in its recieve buffer for it to notice and care. As for the JDBC driver - it turns out that the documentation is out-of-date and/or misleading. The JDBC driver *does* support reading notifications the backend has pushed to its receive buffer, and does *not* have to poll the backend or issue a statement to receive notifications. Some searching suggests that this changed in 8.0 or 8.1 . The documentation needs adjusting, so I've sent a patch to it off to the JDBC folks. -- Craig Ringer
On 11/12/2009 11:39 PM, Craig Ringer wrote: > As for the JDBC driver - it turns out that the documentation is > out-of-date and/or misleading. The JDBC driver *does* support reading > notifications the backend has pushed to its receive buffer, and does > *not* have to poll the backend or issue a statement to receive > notifications. Update: The above is true only for non-SSL connections. For SSL connections you still have to send dummy statements. -- Craig Ringer