Обсуждение: Notifications

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> I'm having problems receiving NOTIFY messages when outside our local =
> network.
> I have dissabled firewall setting but nothing seems to fix the problem.
> I was wandering if NOTIFY messages use UDP or TCP, as that would =
> possibly explain why they are not arriving?

You're certainly barking up the wrong tree.  NOTIFY messages go through
your regular database connection --- if you are able to connect to the
database at all, you can get NOTIFY messages.

            regards, tom lane

Hmm I thought as much but I'm not really that up on socket connections to know
whether a connection could share both TCP and UPD packet types.
This leaves me rather clueless as to why I don't get NOTIFY messages accross
the internet yet I can connect, send and receive sql statements no problems.
Actually thinking about it now - connections to our sever accross the internet
use SSL whilst local connections don't.
Could there be some sort of problem with SSL ?
I guess I can change the software to force local users to use SSL as another
test.

Other than that I'm lost as to what is happening to the messages....

Regards
Donald Fraser

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Donald Fraser" <postgres@kiwi-fraser.net>
Cc: "[ADMIN]" <pgsql-admin@postgresql.org>
Sent: Thursday, December 16, 2004 4:41 PM
Subject: Re: [ADMIN] Notifications


> "Donald Fraser" <postgres@kiwi-fraser.net> writes:
>> I'm having problems receiving NOTIFY messages when outside our local =
>> network.
>> I have dissabled firewall setting but nothing seems to fix the problem.
>> I was wandering if NOTIFY messages use UDP or TCP, as that would =
>> possibly explain why they are not arriving?
>
> You're certainly barking up the wrong tree.  NOTIFY messages go through
> your regular database connection --- if you are able to connect to the
> database at all, you can get NOTIFY messages.
>
> regards, tom lane
>

On Thu, Dec 16, 2004 at 02:20:07PM -0000, Donald Fraser wrote:

> I'm having problems receiving NOTIFY messages when outside our local network.

Define "problems."  What are you doing, what are you expecting to
happen, and what actually does happen?  Have you tested whether you
have the same problems when *inside* the local network?  The
distinction between inside or outside may be a red herring.

> I have dissabled firewall setting but nothing seems to fix the problem.

What firewall setting did you disable and why?  Best not to mess
around with things unless you have reason to think they'll work
(in other words, avoid using a stab-in-the-dark approach).

> I was wandering if NOTIFY messages use UDP or TCP, as that would
> possibly explain why they are not arriving?

PostgreSQL's NOTIFY messages ride inside your connection to the
database, so if you're connected across a network then they'll be
TCP.  But you shouldn't have to do anything special to allow them:
if you can connect to the database then you'll get notifications.

A possible problem is that a firewall or other intermediate device
times out idle connections.  Do you notice the problem after periods
of no activity?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> Could there be some sort of problem with SSL ?

No, I don't believe that one either.

As Michael Fuhr says, you need to be a lot more specific about what
problem you are seeing.

            regards, tom lane

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
Sent: Thursday, December 16, 2004 5:07 PM
Subject: Re: [ADMIN] Notifications


> On Thu, Dec 16, 2004 at 02:20:07PM -0000, Donald Fraser wrote:
>
>> I'm having problems receiving NOTIFY messages when outside our local
>> network.
>
> Define "problems."  What are you doing, what are you expecting to
> happen, and what actually does happen?  Have you tested whether you
> have the same problems when *inside* the local network?  The
> distinction between inside or outside may be a red herring.

Ok the problem is that outside our local network, and more precise on the
outside of our firewall, users do not receive NOTIFY messages. The exact same
software running on the local network, albeit not using SSL does receive NOTIFY
messages.

>> I have dissabled firewall setting but nothing seems to fix the problem.
>
> What firewall setting did you disable and why?  Best not to mess
> around with things unless you have reason to think they'll work
> (in other words, avoid using a stab-in-the-dark approach).

I didn't change anything on our corporate firewall - it has tunnels through so
that we can connect to the database, what I was disabling was the microsoft
firewall on a PC connected to the database.

>> I was wandering if NOTIFY messages use UDP or TCP, as that would
>> possibly explain why they are not arriving?
>
> PostgreSQL's NOTIFY messages ride inside your connection to the
> database, so if you're connected across a network then they'll be
> TCP.  But you shouldn't have to do anything special to allow them:
> if you can connect to the database then you'll get notifications.
>
> A possible problem is that a firewall or other intermediate device
> times out idle connections.  Do you notice the problem after periods
> of no activity?

The problem is immediate, I can instanciate the sending of a NOTIFY message
from my own connection and I don't receive a return message yet users on the
local network do.

Basically now that I know NOTIFY uses TCP and therefore "should" clearly not be
getting lost anywhere, I will do a lot more investigating into our software as
that is the most likely place for the proble!. It just seems a bit strange that
the same software can receive messages when on the local network... The one
thing I can be sure of is that it will be software! I think the clue must be
the setting up of SSL on our software....

Thanks for the input,
regards
Donald Fraser.

> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

On Thu, Dec 16, 2004 at 05:44:09PM -0000, Donald Fraser wrote:
>
> Ok the problem is that outside our local network, and more precise on the
> outside of our firewall, users do not receive NOTIFY messages. The exact
> same software running on the local network, albeit not using SSL does
> receive NOTIFY messages.

As with the inside-vs-outside issue, SSL might be a red herring.
It's curious that the same software behaves differently in different
locations, but be careful not to use tunnel vision when investigating
the cause.

Is there *anything* else that's different between the systems whose
connections work and those whose don't?

Is it possible that the outside users are connecting to a different
database server than the inside users?

What OS are you using, what language, what database interface?  How
are you checking for NOTIFY messages?

> The problem is immediate, I can instanciate the sending of a NOTIFY message
> from my own connection and I don't receive a return message yet users on
> the local network do.

Have you done simple tests with psql?  You'd probably have to issue
a query before seeing the notification, but that should still tell
you if the notification arrived at all.

> Basically now that I know NOTIFY uses TCP and therefore "should" clearly
> not be getting lost anywhere, I will do a lot more investigating into our
> software as that is the most likely place for the proble!. It just seems a
> bit strange that the same software can receive messages when on the local
> network... The one thing I can be sure of is that it will be software! I
> think the clue must be the setting up of SSL on our software....

Again, beware of putting on blinders.  If the database connection
is otherwise working then it would be strange if SSL configuration
were causing the problem.  *Is* the connection otherwise working?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hello again,
I had to rush off to a Christmas meal last night, hence the late reply....

Well today I looked extensively into the problem and discovered that the NOTIFY
messages are arriving outside the firewall.
I used psql which itself connected to the database with ssl and received its
own NOTIFY messages ok
.
Looking at our software, which is java, the problem lies in the implementation
of receiving NOTIFY messages. It uses a separate thread to check the input
stream of the socket for incoming data. The problem is related to ssl, the ssl
input stream (implemented by sun) does not appear to implement the
"available()" method, or at least that is what I'm seeing. According to the
documentation the default implementation always returns 0, where as the non-ssl
input stream definitely does implement the "available()" method which returns
the number of bytes that can be read from the input stream without blocking.
According to sun subclasses of InputStream should implemet the "available()"
method. Why didn't they just make it an abstract method forcing an
implementation - now our code is broken because of it!

This will not be an easy work around....

Thanks again for the input,
regards
Donald Fraser

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Donald Fraser" <postgres@kiwi-fraser.net>
Cc: "[ADMIN]" <pgsql-admin@postgresql.org>
Sent: Thursday, December 16, 2004 6:54 PM
Subject: Re: [ADMIN] Notifications


> On Thu, Dec 16, 2004 at 05:44:09PM -0000, Donald Fraser wrote:
>>
>> Ok the problem is that outside our local network, and more precise on the
>> outside of our firewall, users do not receive NOTIFY messages. The exact
>> same software running on the local network, albeit not using SSL does
>> receive NOTIFY messages.
>
> As with the inside-vs-outside issue, SSL might be a red herring.
> It's curious that the same software behaves differently in different
> locations, but be careful not to use tunnel vision when investigating
> the cause.
>
> Is there *anything* else that's different between the systems whose
> connections work and those whose don't?
>
> Is it possible that the outside users are connecting to a different
> database server than the inside users?
>
> What OS are you using, what language, what database interface?  How
> are you checking for NOTIFY messages?
>
>> The problem is immediate, I can instanciate the sending of a NOTIFY message
>> from my own connection and I don't receive a return message yet users on
>> the local network do.
>
> Have you done simple tests with psql?  You'd probably have to issue
> a query before seeing the notification, but that should still tell
> you if the notification arrived at all.
>
>> Basically now that I know NOTIFY uses TCP and therefore "should" clearly
>> not be getting lost anywhere, I will do a lot more investigating into our
>> software as that is the most likely place for the proble!. It just seems a
>> bit strange that the same software can receive messages when on the local
>> network... The one thing I can be sure of is that it will be software! I
>> think the clue must be the setting up of SSL on our software....
>
> Again, beware of putting on blinders.  If the database connection
> is otherwise working then it would be strange if SSL configuration
> were causing the problem.  *Is* the connection otherwise working?
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Fri, Dec 17, 2004 at 01:01:34PM -0000, Donald Fraser wrote:

> Looking at our software, which is java, the problem lies in the
> implementation of receiving NOTIFY messages. It uses a separate thread to
> check the input stream of the socket for incoming data.  The problem is

Are you just looking for incoming data, or are you also checking
that the data indeed contains a notification?

> related to ssl, the ssl input stream (implemented by sun) does not appear
> to implement the "available()" method, or at least that is what I'm seeing.
> According to the documentation the default implementation always returns 0,
> where as the non-ssl input stream definitely does implement the
> "available()" method which returns the number of bytes that can be read
> from the input stream without blocking. According to sun subclasses of
> InputStream should implemet the "available()" method. Why didn't they just
> make it an abstract method forcing an implementation - now our code is
> broken because of it!

Do you have another way to check if the underlying socket is readable?
In C using libpq you'd get the connection's file descriptor with
PQsocket() and then call select() or poll() to wait for it to become
readable.  Can you do that in Java?  Apparently its available()
method abstracts those calls and adds some magic of its own -- can
you bypass available() and get at the lower-layer functions?

I wonder if there would be merit in PostgreSQL providing a server-side
function that you could use like this:

SELECT * FROM wait_for_notifies();
  pid  | condition
-------+-----------
 77578 | test
(1 row)

This would provide a simple interface for clients that want to block
until a notification arrives, one that wouldn't require any special
handling by the underlying client library.

Thoughts from the developers?  Has such a thing been proposed before?
Are there implementation issues that would make it difficult or
impossible to do?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Just a final follow up to this message thread - Sun Microsystems confirm
that there is a bug with their implementation of the ssl input stream socket
in the 1.4 series JVM.

Regards
Donald Fraser

----- Original Message -----
From: "Sun Microsystems"
Sent: Friday, December 31, 2004 9:49 PM
Subject: Re: (Incident Review ID: 358430) available() method of SSLSocket
InputStream not implemented


> --- Note: you can send us updates about your Incident ---
> --- by replying to this mail.  Place new information  ---
> --- above these lines.  Do not include attachments.   ---
> --- Our system ignores attachments and anything below ---
> --- these lines.                                      ---
>
> Hi Donald Fraser,
>
> This issue is related to Bug ID: 4072863.  Thank you for providing
additional information for our investigation.
>
> You can review this bug and look for related issues on the Java Developer
Connection Bug Database at:
> http://developer.java.sun.com/developer/bugParade/
> and
> http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4072863
>
> Bug 4072863 seems to be related to this problem, but it does not
specifically talk about SSL Sockets. However there is a very good post from
wetmore on the forums available at
> http://forum.java.sun.com/thread.jspa?forumID=2&threadID=423967 that
brings perspective to your problem.
>
> The problem related to the available method returning 0 has been discussed
in various forums. Do check the various discussion threads available in the
forums. However if you feel that this does not answer your query, please
feel free to submit a new report with any additional information you may
have.  If you send me the ID of the new incident, I will try to expedite the
reporting process.
>
> Thanks for taking the time to report this problem.
>
> Regards,
> Nelson

----- Original Message -----
From: "Donald Fraser"
To: "[ADMIN]" <pgsql-admin@postgresql.org>
Sent: Friday, December 17, 2004 1:01 PM
Subject: Re: [ADMIN] Notifications


> Hello again,
> I had to rush off to a Christmas meal last night, hence the late reply....
>
> Well today I looked extensively into the problem and discovered that the
NOTIFY
> messages are arriving outside the firewall.
> I used psql which itself connected to the database with ssl and received
its
> own NOTIFY messages ok
> .
> Looking at our software, which is java, the problem lies in the
implementation
> of receiving NOTIFY messages. It uses a separate thread to check the input
> stream of the socket for incoming data. The problem is related to ssl, the
ssl
> input stream (implemented by sun) does not appear to implement the
> "available()" method, or at least that is what I'm seeing. According to
the
> documentation the default implementation always returns 0, where as the
non-ssl
> input stream definitely does implement the "available()" method which
returns
> the number of bytes that can be read from the input stream without
blocking.
> According to sun subclasses of InputStream should implemet the
"available()"
> method. Why didn't they just make it an abstract method forcing an
> implementation - now our code is broken because of it!
>
> This will not be an easy work around....
>
> Thanks again for the input,
> regards
> Donald Fraser