Re: Cheapest way to poll for notifications? & Driver improvement question re SSL and notify

Maciek Sakrejda wrote:
>> So my question is: is there any particularly low-overhead statement that
>> might be suitable for generating pointless client/server chat to check for
>> received async notifications? Should I just use "SELECT 1" ? Or would I be
>> better off using a SHOW statement like "SHOW role" to avoid creating a
>> snapshot etc?
>>
>
> Does the jdbc driver work with EmptyQueryResponse?

It seems to.

> At the protocol
> level, if you send a Query message with an all-whitespace sql string,
> the backend is supposed to reply with a single EmptyQueryResponse
> message and then issue ReadyForQuery again. I have a feeling that jdbc
> is probably "too smart" for this to work, but it could be worth a
> shot.

The JDBC driver isn't too smart for this - it doesn't catch the empty
query, but instead merrily passes it on to the backend as part of an
extended query request.

See the attached text file - it shows the client/server chat for one
such round trip.

So: the server doesn't consider the empty query an error, so it won't
affect any currently running transactions. It's a cheap single-message
each way round-trip, and it doesn't have any planning or snapshot
creation costs. Looks good to me :-)

Since I can't imagine an app _needing_ the JDBC driver to ignore empty
queries, is this something that it might be reasonable to document as
expected behavior from the driver - that it _will_ send even empty
queries to the server?

Thanks _VERY_ much for the tip. You've saved me and my logs quite a bit
of pain. It's not as good as client-side-only polling, but it's a whole
nicer than a full dummy statement, and I can easily do it only when an
SSL connection is detected.



--
Craig Ringer
No.     Time        Source                Destination           Protocol Info
    178 40.633175   127.0.0.1             127.0.0.1             PGSQL    >P/B/D/E/S

Frame 178 (110 bytes on wire, 110 bytes captured)
Ethernet II, Src: 00:00:00_00:00:00 (00:00:00:00:00:00), Dst: 00:00:00_00:00:00 (00:00:00:00:00:00)
Internet Protocol, Src: 127.0.0.1 (127.0.0.1), Dst: 127.0.0.1 (127.0.0.1)
Transmission Control Protocol, Src Port: 59141 (59141), Dst Port: postgresql (5432), Seq: 3574, Ack: 53702, Len: 44
PostgreSQL
    Type: Parse
    Length: 8
    Statement:
    Query:
    Parameters: 0
PostgreSQL
    Type: Bind
    Length: 12
    Portal:
    Statement:
    Parameter formats: 0
    Parameter values: 0
    Result formats: 0
PostgreSQL
    Type: Describe
    Length: 6
    Portal:
PostgreSQL
    Type: Execute
    Length: 9
    Portal:
    Returns: all rows
PostgreSQL
    Type: Sync
    Length: 4

No.     Time        Source                Destination           Protocol Info
    179 40.633290   127.0.0.1             127.0.0.1             PGSQL    <1/2/n/I/Z

Frame 179 (92 bytes on wire, 92 bytes captured)
Ethernet II, Src: 00:00:00_00:00:00 (00:00:00:00:00:00), Dst: 00:00:00_00:00:00 (00:00:00:00:00:00)
Internet Protocol, Src: 127.0.0.1 (127.0.0.1), Dst: 127.0.0.1 (127.0.0.1)
Transmission Control Protocol, Src Port: postgresql (5432), Dst Port: 59141 (59141), Seq: 53702, Ack: 3618, Len: 26
PostgreSQL
    Type: Parse completion
    Length: 4
PostgreSQL
    Type: Bind completion
    Length: 4
PostgreSQL
    Type: No data
    Length: 4
PostgreSQL
    Type: Empty query
    Length: 4
PostgreSQL
    Type: Ready for query
    Length: 5
    Status: Idle (73)