Using a different JDBC Driver in the Master

The master's connectivity to the datanodes (with no agents) appears to use edb-jdbc14.jar.
Anyone ever try to change that out to be another JDBC Type 4 driver?
Or is its interface to the master not the standard jdbc interface?

The reason is that if its changeable, one could try Continuuent's postgresql jdbc driver which talks to two masters at the same time and have gridsql survive node failure automatically. (maybe... plenty of details here, like how to handle that there isnt 1 backend database but N.)

Another option is to code duplicate writes in gridsql-master, any write to database Node 5 actually goes to 5 and "5-Backup". Reads can come from either.
When one fails, the other is still available.
The whole cluster must be shutdown to restore connectivity to the first.

Definitely agree it should be kept simple, its still a mostly offline non-HA system.

I have not tried it out, but it would be interesting to try.

A long time ago I read up on Continuent. I believe there were some limitations, like you could not load data via COPY. Perhaps this problem has since been take care of.

If you do try it out, please share your experiences here.

Regards,

Mason

Hi Mason,

This is kind of related - I was trying to switch to the latest Postgres driver for the gs-server.sh script - I wanted to do this to try to fix the issue described here (http://archives.postgresql.org/pgsql-jdbc/2007-09/msg00107.php) - Basically, I have a timestamp column which is sometimes null and sometimes has a value (so the program will sometimes do preparedStatement.setNull(..) or preparedStatement.setTimestamp(..) - When I try to insert the data via GridSQL I get the following exception:

2010-04-13 17:27:32,295 WARN DataBase | batchInsert: Exception: Can't change resolved type for param: 64 from 1184 to 1114
java.lang.IllegalArgumentException: Can't change resolved type for param: 64 from 1184 to 1114
at org.postgresql.core.v3.SimpleParameterList.setResolvedType(SimpleParameterList.java:230)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:165

at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1055)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:39

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:270

at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:881)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:619)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:866)

If I use the Postgres driver (latest 8.4 driver) and point my program to bypass GridSQL and directly insert into the node database (Postgres 8.4), the inserts are successful.

Is this a GridSQL issue / edbJDBC driver issue? Would it be something that will be fixed in the near future?

My attempt at replacing the edb-jdbc14.jar with the latest postgres 8.4 jdbc jar (postgresql-8.4-701.jdbc4.jar) in the gs-server.sh script did not fix the problem for me - so I suspect it is a GridSQL code issue and not a edb-jdbc14 Driver issue.

Regards,

Ranga

Hi Ranga,

Please try to update the Postgres driver registered with the Spring framework.
If this won't help, please let us know:
- Driver version
- GridSQL version
- Statement(s) executed in the batch
- Target table metadata

Hi Andrei,

Thanks for your quick response. The code is a bit difficult for me to post - due to a whole bunch of abstraction layers. However, I will try to explain with better detail to make the issue clear.

GridSQL Version: 2.0 Beta

1. I believe the issue is similar to the issue discussed here: http://archives.postgresql.org/pgsql-jdbc/2007-09/msg00107.php. The problem does not happen unless using a larger table (large row size, about 300 columns, total 260 rows all written out in a single jdbc batch via Spring JdbcTemplate). The SQL itself is a simple insert statement - "Insert into TABLE_NAME VALUES(?,?,?....)" - Batch size is 500, but for my data all 260 rows are written out in a single batch. I don't think this is related to jdbc batching, since I also tried with the batch size to 1 and still observed the problem.

2. The table I am facing this issue with has a timestamp column (timestamp(6) without time zone) which has null allowed, and the data that I am trying to insert has about 31 rows with a null value and remaining 229 rows with non-null values for the timestamp. (This table actually has several columns with timestamp type and there could have been other columns with null values for the timestamp - but I specifically was focusing on this particular column which I knew had some null values in the data).

Here is a simple view of what the code was doing - If the value is a null, we call setNull() and if the value is not null, we call setTimestamp() on the used PreparedStatement.

3. Driver versions I tried with to connect to the database: edb-jdbc14.jar driver that came with GridSQL 2.0 beta and latest postgresql driver - postgresql-8.4-701.jdbc4.jar.

4. The configuration is simple: running on my laptop with the GridSQL coordinator in front of a single Postgres 8.4 database instance.

Problem description:

I see the above exception when trying to insert the data via GridSQL - i.e. when my my program connects to GridSQL (using the URL: jdbc:postgresql://localhost:6453/ciqdb) and does the inserts.

When I switch the URL to point to the underlying Postgres database (using the URL: jdbc:postgresql://localhost:5432/ciqdbN1) , the inserts work fine.

Workaround:

I am currently proceeding with my development using a workaround - I am inserting a default value for the timestamp (say Jan 1, 1970) - and have modified the API (that my application will use) to look for the default value in case of timestamp columns and return null instead. This is possible for me since my application data is such that *real* data will always have recent timestamps (within a couple of months of present time) - However, if there other scenarios where the value is not obvious, this kind of work around may not be possible. In addition, of course I am adding to the table's space usage unnecessarily.

Hi Ranga,

We reproduced the issue.
We will look into and get back to you.

Thanks,
Andrei

Ranga,

FYI, we have fixed the bug and checked in code to CVS.

Thanks,
Andrei