Обсуждение: Problem with Async (multiple) notifications

I've been playing with async notifications lately, and I've found a
small problem: when several notifications are issued at once (for
example within a transaction) normally only one of those gets to the
client.  The rest of the notifications are not received until
something happens on the connection, for example a query or another
notification.  I'm using the latest CVS, updated yesterday.  I've
looked over the code, and there's nothing obvious. I think that this
has to do with java's handling of sockets, as the C example that deals
with notifications works fine.  It seems that some packages are
"stuck" in the socket, but my knowledge of sockets is very limited.  A
possible workarround would be to issue the simplest possible query or
message to the backend when a notification is found, to see if anymore
notifications are "stuck". This would generate a little bit of extra
traffic, but only when there is a notification.  Here is a small test
case showing what's happening:

public class TestAsync extends TestCase {

    Connection  conn2,conn1;

    public void testAsync() {
        String user = "andres";
        String pass = "";
        String url = "jdbc:postgresql://127.0.0.1/template1";
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException cnf) {
            fail("Data Base Driver not Found: " + cnf.getMessage());

        }
        try {

            conn1 = DriverManager.getConnection(url, user, pass);
            conn2 = DriverManager.getConnection(url, user, pass);
            conn2.setAutoCommit(true);

            Statement stmt2 = conn2.createStatement();
            stmt2.execute("LISTEN test1");
            stmt2.execute("LISTEN test2");
            stmt2.execute("LISTEN test3");
            PGConnection pgconn = (PGConnection) conn2;


            Statement stmt1=conn1.createStatement();
            stmt1.execute("NOTIFY test1;");
            stmt1.execute("NOTIFY test2;");
            stmt1.execute("NOTIFY test3;");
            conn1.commit();


           PGNotification[] nots = pgconn.getNotifications();

                assertEquals(3,nots.length);


        } catch (SQLException sqle) {
            fail("SQLException sql" + sqle.getLocalizedMessage());
        }


    }

I'm running a 7.4 backend, but I don't think that's the problem.  But
if someone thinks I'm doing something wrong, please let me know!!
thanks to all of the people that have worked on PostreSQL

Andres Olarte

Andres Olarte wrote:
> I've been playing with async notifications lately, and I've found a
> small problem: when several notifications are issued at once (for
> example within a transaction) normally only one of those gets to the
> client.  The rest of the notifications are not received until
> something happens on the connection, for example a query or another
> notification.

Ok, this looks like a bug in some newish code that allows you to do this
at all (previously, you *always* had to issue a query to notice async
notifications). It's checking only for new data available on the
underlying socket, and not our input buffer -- if we read multiple
notifications into our input buffer, consuming all the data at the
socket level, we only process one before returning to the client, and
don't look for any more until more data arrives at the socket level.

I've applied a fix to CVS HEAD (only org/postgresql/core/PGStream.java
affected), can you try that out?

-O

Andres Olarte <olarte.andres@gmail.com> writes:
> I've been playing with async notifications lately, and I've found a
> small problem: when several notifications are issued at once (for
> example within a transaction) normally only one of those gets to the
> client.

The documentation:
http://www.postgresql.org/docs/8.0/static/sql-notify.html
quoth:
> NOTIFY behaves like Unix signals in one important respect: if the same
> notification name is signaled multiple times in quick succession,
> recipients may get only one notification event for several executions of
> NOTIFY. So it is a bad idea to depend on the number of notifications
> received. Instead, use NOTIFY to wake up applications that need to pay
> attention to something, and use a database object (such as a sequence)
> to keep track of what happened or how many times it happened.

Are you sure you are following this advice?

(In particular, I can absolutely positively guarantee that N NOTIFYs
executed within a single transaction have the same effect as one
NOTIFY.  This is not a bug, it is the design intention.)

            regards, tom lane

Tom Lane wrote:

> (In particular, I can absolutely positively guarantee that N NOTIFYs
> executed within a single transaction have the same effect as one
> NOTIFY.  This is not a bug, it is the design intention.)

Andres was using NOTIFY with different notification names, so I don't
think this is the problem.

-O