Re: [Postgres-xc-general] a new jdbc driver for postgres-xc?

[Michael P. <mic...@gm...>]

On Sat, Jun 9, 2012 at 10:29 AM, Michael Vitale <mic...@ar...> wrote:

> Thanks for the responses.  I'd like to pursue the jdbc driver issue for
> Postgres-xc a bit more...
>
> Regarding the jdbc driver for Postgresql, it would be neat if we could
> take a current cut of the postgresql jdbc driver and customize it for
> postgres-xc, so that the input connection string could contain an array of
> coordinator addresses, just like oracleRAC, so we could have "automatic
> failover" at the coordinator node level embedded right into the jdbc
> driver.  Consider new jdbc pseudo code:
>

I think jdbc driver has already some failover functionalities already
included in it, but honestly not sure.
So it might be that there is already some code in that would help to
support multiple connections to cluster nodes.
However I am not really updated on the development so perhaps such a
feature is not implemented, but I am sure that JDBC guys might be
interested in such a feature to redirect read-only queries to slave nodes
for example just for vanilla postgres.

Btw, the functionality you propose would be helpful for both projects.
You should have a look here to get in touch with the latest development:
http://github.com/pgjdbc/pgjdbc.


>
> If sql connect error encountered, see if the connection string contains
> another coordinator address and if so, connect to that coordinator and set
> its handle globally to replace the original connection handle so other
> threads will use it as well for future IO requests to the database.
>
> Now of course this doesn't handle failover at the data node level, but is
> seems like it would sure do the trick at the coordinator node level.
>
Yes that would be helpful. But be sure to check if there is not already
some infrastructure in jdbc itself.

Even if automatic failover on Datanodes is a core feature, there are
already some ways to check if a Datanode is alive or not from a
Coordinator, so you could also know when a Datanode is down and promote a
slave attached to it.
There is a functionality called EXECUTE DIRECT whose doc is here:
http://postgres-xc.sourceforge.net/docs/1_0/sql-executedirect.html
This SQL specific to XC allows to launch a query directly to a remote node,
Coordinator or Datanode when connected on a Coordinator.

For example, let's imagine that your driver is connected to Coordinator
called node_coord1, and you have Datanodes called node_dn1, node_dn2, ...
node_dnN.
You can simply check if the node (here node_dn2) is alive by launching
something like:
EXECUTE DIRECT ON NODE node_dn2 'SELECT 1';
If the node is offline for a reason or another pooler will complain telling
that it cannot get a connection to remote node.

You can also use that from a Coordinator to check if a remote Coordinator
is down.


>
> Now let me compare this new postgres-xc jdbc driver to HA failover already
> embedded in JBOSS AS 7.  It would be much better, because while JBOSS would
> allow you to input multiple jdbc connection strings (using the vertical bar
> separator), it does not break down bad connections and automatically
> reconnect those clients to the other one.  Instead it will just make sure
> that future connection handle requests are directed to the other one.
>  Already connected users would have to manually logoff from their
> applications and log on again.  But if we embed multiple coordinators into
> a new Postgres-xc jdbc driver, then the driver will break down the bad
> connections and automatically connect those users to the other cordinators
> without the users having to do anything!
>
> Although I'm calling this a new jdbc driver for postgres-xc, the concept
> would work the same for postgresql and postgres-xc: jdbc driver allows
> specification of multiple database connection parameters.
>
> What you all think?  And if you all know where this would be useful to a
> postgresql jdbc community email address, please forward this.
>
So it would be nice.
Your module should be pluggable on top of jdbc, or even directly included
in jdbc if you consider the case of normal postgreSQL with master and slave
nodes (slave nodes are read-only) for read-query balancing.
-- 
Michael Paquier
http://michael.otacoo.com