Connecting to NOTIFY with telnet

Hi,

On 2019-05-03 11:06:09 -0700, Igal Sapir wrote:

Is it possible to connect to Postgres for notifications via telnet? This
is obviously more for learning/experimenting purposes.

No. The protocol is too complicated to make that realistically doable /
useful.

I expected a simple way to connect and consume notifications but can not
find any example or documentation on how to do that.

If you really wanted to go down that road, you'd have to read the
protocol specs. It'd not make sense to document how-to steps for
something as low-level as this.

Greetings,

Andres Freund

Thank you both (Michel replied off-list it seems),

On Fri, May 3, 2019 at 1:10 PM Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2019-05-03 11:06:09 -0700, Igal Sapir wrote:

Is it possible to connect to Postgres for notifications via telnet? This
is obviously more for learning/experimenting purposes.

No. The protocol is too complicated to make that realistically doable /
useful.

I expected a simple way to connect and consume notifications but can not
find any example or documentation on how to do that.

If you really wanted to go down that road, you'd have to read the
protocol specs. It'd not make sense to document how-to steps for
something as low-level as this.

My main "issue" is that the official pgjdbc driver does not support the
notifications with listen and I was trying to figure out why.

I know that the pgjdbc-ng [1]http://impossibl.github.io/pgjdbc-ng/ driver does support that feature, but I'm not
sure how many people use it etc. It looks like it's being maintained with
a recent release from a couple of months ago so that's good.

I thought that given the fact that it's just a socket connection it might
be fairly simple to open a connection and listen.

Best,

Igal

[1]: http://impossibl.github.io/pgjdbc-ng/

## Igal Sapir (igal@lucee.org):

My main "issue" is that the official pgjdbc driver does not support the
notifications with listen and I was trying to figure out why.

https://jdbc.postgresql.org/documentation/head/listennotify.html

Regards,
Christoph

--
Spare Space

Christoph,

On Sat, May 4, 2019 at 10:44 AM Christoph Moench-Tegeder <cmt@burggraben.net>
wrote:

## Igal Sapir (igal@lucee.org):

My main "issue" is that the official pgjdbc driver does not support the
notifications with listen and I was trying to figure out why.

https://jdbc.postgresql.org/documentation/head/listennotify.html

I should have been more explicit. My main issue is with the following
statement from the link that you posted:

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

Polling is much less efficient than event handling and I'm sure that
there's a major performance hit with that.

Thanks,

Igal

On Sat, May 4, 2019 at 1:49 PM Igal Sapir <igal@lucee.org> wrote:

Christoph,

On Sat, May 4, 2019 at 10:44 AM Christoph Moench-Tegeder <
cmt@burggraben.net> wrote:

## Igal Sapir (igal@lucee.org):

My main "issue" is that the official pgjdbc driver does not support the
notifications with listen and I was trying to figure out why.

https://jdbc.postgresql.org/documentation/head/listennotify.html

I should have been more explicit. My main issue is with the following
statement from the link that you posted:

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

Polling is much less efficient than event handling and I'm sure that
there's a major performance hit with that.

Isn't that addressed here?:

// If this thread is the only one that uses the connection,
a timeout can be used to
// receive notifications immediately:
// org.postgresql.PGNotification notifications[] =
pgconn.getNotifications(10000);

Cheers,

Jeff

Jeff,

On Sat, May 4, 2019 at 11:34 AM Jeff Janes <jeff.janes@gmail.com> wrote:

On Sat, May 4, 2019 at 1:49 PM Igal Sapir <igal@lucee.org> wrote:

Christoph,

On Sat, May 4, 2019 at 10:44 AM Christoph Moench-Tegeder <
cmt@burggraben.net> wrote:

## Igal Sapir (igal@lucee.org):

My main "issue" is that the official pgjdbc driver does not support the
notifications with listen and I was trying to figure out why.

https://jdbc.postgresql.org/documentation/head/listennotify.html

I should have been more explicit. My main issue is with the following
statement from the link that you posted:

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

Polling is much less efficient than event handling and I'm sure that
there's a major performance hit with that.

Isn't that addressed here?:

// If this thread is the only one that uses the
connection, a timeout can be used to
// receive notifications immediately:
// org.postgresql.PGNotification notifications[] =
pgconn.getNotifications(10000);

It "helps", but it's still not the same as keeping the connection open and
receiving messages in real time.

Looking at the documentation of pgjdbc-ng [1]http://impossibl.github.io/pgjdbc-ng/docs/current/user-guide/#extensions-notifications it looks quite impressive.
I'm thinking to try it out and possibly even try to benchmark it for
performance against the official JDBC driver.

Igal

[1]: http://impossibl.github.io/pgjdbc-ng/docs/current/user-guide/#extensions-notifications
http://impossibl.github.io/pgjdbc-ng/docs/current/user-guide/#extensions-notifications

On Sat, May 4, 2019 at 3:04 PM Igal Sapir <igal@lucee.org> wrote:

Jeff,

On Sat, May 4, 2019 at 11:34 AM Jeff Janes <jeff.janes@gmail.com> wrote:

On Sat, May 4, 2019 at 1:49 PM Igal Sapir <igal@lucee.org> wrote:

Christoph,

On Sat, May 4, 2019 at 10:44 AM Christoph Moench-Tegeder <
cmt@burggraben.net> wrote:

## Igal Sapir (igal@lucee.org):

My main "issue" is that the official pgjdbc driver does not support

the

notifications with listen and I was trying to figure out why.

https://jdbc.postgresql.org/documentation/head/listennotify.html

I should have been more explicit. My main issue is with the following
statement from the link that you posted:

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

Polling is much less efficient than event handling and I'm sure that
there's a major performance hit with that.

Isn't that addressed here?:

// If this thread is the only one that uses the
connection, a timeout can be used to
// receive notifications immediately:
// org.postgresql.PGNotification notifications[] =
pgconn.getNotifications(10000);

It "helps", but it's still not the same as keeping the connection open and
receiving messages in real time.

But it is the same as that. It keeps the connection open, and receives the
messages in real time. It blocks either until it gets a NOTIFY, or for 10
seconds, whichever occurs first. You can use 0 to block forever until a
NOTIFY arrives. Maybe you didn't remove the sleep a few lines further
down? Or is there some other problem here?

Cheers,

Jeff

Jeff,

On Sat, May 4, 2019 at 2:10 PM Jeff Janes <jeff.janes@gmail.com> wrote:

On Sat, May 4, 2019 at 3:04 PM Igal Sapir <igal@lucee.org> wrote:

Jeff,

On Sat, May 4, 2019 at 11:34 AM Jeff Janes <jeff.janes@gmail.com> wrote:

On Sat, May 4, 2019 at 1:49 PM Igal Sapir <igal@lucee.org> wrote:

<snip/>

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

Polling is much less efficient than event handling and I'm sure that
there's a major performance hit with that.

Isn't that addressed here?:

// If this thread is the only one that uses the
connection, a timeout can be used to
// receive notifications immediately:
// org.postgresql.PGNotification notifications[] =
pgconn.getNotifications(10000);

It "helps", but it's still not the same as keeping the connection open
and receiving messages in real time.

But it is the same as that. It keeps the connection open, and receives
the messages in real time. It blocks either until it gets a NOTIFY, or for
10 seconds, whichever occurs first. You can use 0 to block forever until a
NOTIFY arrives. Maybe you didn't remove the sleep a few lines further
down? Or is there some other problem here?

I didn't try it out, but I know from experience with other systems that
long polling is not as efficient as asynchronous events over an open
socket. For example, Comet vs. WebSockets in web servers.

The only way to find out how big, or small, the impact is, is to benchmark
both implementations, but I have no doubt that the open socket will be more
efficient.

Best,

Igal