5 Replies Latest reply on May 15, 2006 9:33 AM by Borut ?agar

    RT: PostgreSQL driver

    Andrew Oliver Master

      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


      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?