RT: PostgreSQL driver
acoliver Feb 10, 2006 11:07 AMBoth 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
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:
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:
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?