2

We are trying to use pgpool 3.2.1 in parallel mode to split a database table across multiple servers. However, when using java, it seems to only do replication mode and not parallel mode.

In testing, we are using 3 backend servers running Postgres 8.3 and a test database with 300 rows in the table to be distributed.

When we create and query the database using sql statements (i.e., from the psql shell), everything seems to be working fine. Querying the system server shows 300 rows, and querying the 3 backend servers shows that each have 100 rows.

However, creating the same database from a java application causes the inserts to be replicated instead of distributed, so each backend server has all 300 rows. Querying the system server in java correctly shows 300 rows, but querying the system server in psql combines the results and shows 900 rows.

It seems that the parallel mode is ignored in java and it instead uses replication mode. There doesn't seem to be anything printed in the pgpool logs about this and I don't get any error in java except that the data isn't distributed among the backend servers.

I find the following restriction listed in the pgpool manual: http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html#restriction

Extended Query Protocol (for parallel mode)

The extended query protocol used by JDBC drivers, etc. is not supported. The simple query protocol must be used. This means you can't use prepared statements.

The java program does use prepared statements and it might not be an option to not use prepared statements.

There is a similar question about pgpool and prepared statements: Java queries against PGPool II cause "unnamed prepared statement does not exist" errors

But adding protocolVersion=2 to the JDBC URL causes insert statements in java to not finish.

Has anyone experienced this issue? Is there a possible work around?

Thanks

Community
  • 1
  • 1
anln
  • 21
  • 2
  • Statement `Extended Query Protocol (for parallel mode)- The extended query protocol used by JDBC drivers, etc. is not supported. The simple query protocol must be used. This means you can't use prepared statements.` is wrong according to this http://lists.pgfoundry.org/pipermail/pgpool-general/2011-June/003701.html – Viraj Nov 13 '14 at 10:34

1 Answers1

1

I have the same problem. I'm trying to work around it using the libpq C library instead of the JDBC driver.

I made a wrapper of the C library in Java through SWIG/JNI and now it seems working correctly.

You can see the results of my experiments in this post of my blog.

I don't know if the same solution could be applicable in your case. I have very few (and isolated) tables to partition, so I will use the licpq wrapper in these specific situations. For the other parts of the application I will continue to use the JDBC driver, connecting directly to a single PostgreSQL backend (not through PGPool).

Lucio Benfante
  • 99
  • 1
  • 2