RT: PostgreSQL driver

[acoliver]

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?

[mikezzz]

I've posted a patch to the pgjdbc mailing list. I'll let you know when I get a response.

Mike.

[acoliver]

Thanks mike! FYI everyone, my tests of opening and closing a connection in a pool:

PGTest1 (SELECT 1) 1000 iterations: 2491ms

PGTest2 (ping()) 1000 iterations: 63ms

granted 2.5 seconds for 1000 connections is not earth shattering by any means (and much smaller than say disabling RMI DGC: http://jboss.org/jbossBlog/blog/acoliver/2006/03/21/If_you_dont_do_this_JBoss_will_run_really_slowly.txt or setting your minium DB pool size so that it is like...actually pooled), but it can be at least somewhat significant on busy systems with lots of concurrent transactions.

Once Mike gets a response I have a PostgreSQLValidConnectionChecker ready to commit.

-Andy

[mikezzz]

We have had a slight change of plans. PostgreSQL supports an Empty Query:

Statement stmt = cn.createStatement();
stmt.execute("");

Which is probably about as efficient as the complete ping implementation (my first implemenation was missing a bunch of stuff).

I have written the connection checker and posted a Jira patch (JBAS-3133) for it. It also means we don't need a change to the PostgreSQL driver (simple is good).

Mike.

[acoliver]

this is outstanding.

[zagarb]

Can anyone verify if the following attribute has the same (performance) effect as posted patch:

<check-valid-connection-sql>;</check-valid-connection-sql>

Functionaly I think that it works (validates the connection and forces the reconnect in case of restart).

Tested on JBossAS 4.0.3sp1 and PostgreSQL 8.1.3.

Regards,
Borut