Short open source project for someone interested

Hi All,

So far no one has taken me up on this (below).  I think it would be a 
really cool little day project for someone who hasn't hacked a JDBC 
driver or wants to get their feet we in open source.

A little background.  A "ping" is a DB command which just round trips a 
connection to check that it is open but results in no relevant database 
hit (strictly communication).  For those with oracle knowledge, you've 
probably used "Select 1 from DUAL" to check the status of an open 
connection (especially if you've written a connection pool).  However, 
since the DB side actually is doing some relevant work, this is less 
efficient than the oracle specific call on the OracleConnection object.

Both MySQL and Oracle supply this command.  Postgresql's driver does not 
yet, but I have detailed below how I think it might be implemented. 
Following this it would be interesting to see how much time is saved per 
1,000,000 operations.  If anyone is interested, I'll help them out as 
they have questions.

Thanks,

Andy

Anyhow:

(pretty version w/formatting and stuff
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=77174)

Both MySQL and Oracle include a function called "ping()"

OracleValidConnectionChecker.java and
MySQLValidConnectionChecker.java.

PostgreSQL requires you to do a "select 1" which is probably not efficient:

examples of each *-ds.xml

We can probably fix the PostgreSQL driver at the driver only level and 
implement this. The backend postgresql protocol has a command called synch:


http://developer.postgresql.org/docs/postgres/protocol-message-formats.html

Quote:

Sync (F)

Byte1('S')

Identifies the message as a Sync command.
Int32(4)

Length of message contents in bytes, including self.


which looks like this:

Code:

     private void sendSync() throws IOException {
         if (logger.logDebug())
             logger.debug(" FE=> Sync");

         pgStream.SendChar('S');     // Sync
         pgStream.SendInteger4(4); // Length
         pgStream.flush();
     }
	



It results in a "ReadyForQuery" response from the server:

Quote:

ReadyForQuery (B)

Byte1('Z')

Identifies the message type. ReadyForQuery is sent whenever the backend 
is ready for a new query cycle.
Int32(5)

Length of message contents in bytes, including self.
Byte1

Current backend transaction status indicator. Possible values are 'I' if 
idle (not in a transaction block); 'T' if in a transaction block; or 'E' 
if in a failed transaction block (queries will be rejected until block 
is ended).


Which you can see an example of processing here under processResults 
case 'Z'.

If the ProtocolConnection had a "ping()" command and the 
AbstractJdbc2Connection.java class called it and supplied its own ping() 
which was then in the PGConnection.java interface -- then we'd have what 
constitutes a working ping.

The only question is what postgresql does with a Sync when nothing has 
happened. It seems doubtful that it would ever throw an error, but that 
it would most likely say ReadyForQuery. It seems doubtful that this 
results in any kind of real operation at all...which would make it ideal.

Alternatively a ping could be added to the backend as a real command but 
that seems unnecessary give how lightweight Sync seems. Would anyone be 
willing to give coding this up a try?