10th INSERT fails when using Postgres JDBC driver

[Artem Bakulin]

Hi,

I observe some odd bug when I use JDBC driver to INSERT rows to hash-distributed table. Basically, you cannot do more than 10 INSERTs in one JDBC connection.

This can be easily reproduced with Docker image 5.1.0.

Steps to reproduce:

1) Create two test tables: one hash-distributed, one ordinary:

CREATE TABLE simple_table (id INTEGER PRIMARY KEY NOT NULL, foo VARCHAR(256));
CREATE TABLE hash_table (id INTEGER PRIMARY KEY NOT NULL, foo VARCHAR(256));
SELECT master_create_distributed_table('hash_table', 'id', 'hash');
SELECT master_create_worker_shards('hash_table', 4, 1);

2) Run and compile this Java code with latest Postgres JDBC driver 9.4.1208:

<dependency>
 <groupId>org.postgresql</groupId>
 <artifactId>postgresql</artifactId>
 <version>9.4.1208</version>
</dependency>

import org.postgresql.ds.PGSimpleDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CitusTest {

    public static void main(String[] args) throws SQLException {

        PGSimpleDataSource dataSource = new PGSimpleDataSource();
        dataSource.setUrl("jdbc:postgresql://127.0.0.1:5432/postgres");
        dataSource.setUser("postgres");

        Connection conn = dataSource.getConnection();
        conn.setAutoCommit(true);

        // This works fine
        insert(conn, "simple_table", 20);

        // This throws SQLException on 10th insert
        insert(conn, "hash_table", 20);
    }

    private static void insert(Connection conn, String tableName, int nRows) throws SQLException {
        String SQL = "INSERT INTO %s(id, foo) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET foo=EXCLUDED.foo";
        PreparedStatement stmt = conn.prepareStatement(String.format(SQL, tableName));
        for (int i=0; i<nRows; i++) {
            stmt.setInt(1, i);
            stmt.setString(2, "bar" + i);
            System.out.println("Running query " + i + ": " + stmt);
            stmt.executeUpdate();
        }
    }
}

Every time I run this code, I get SQLException on 10th INSERT to hash-distributed table:

Running query 8: INSERT INTO hash_table(id, foo) VALUES (8, 'bar8') ON CONFLICT (id) DO UPDATE SET foo=EXCLUDED.foo
Running query 9: INSERT INTO hash_table(id, foo) VALUES (9, 'bar9') ON CONFLICT (id) DO UPDATE SET foo=EXCLUDED.foo
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not modify any active placements
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
 at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
 at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
 at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:133)
 at CitusTest.insert(CitusTest.java:33)
 at CitusTest.main(CitusTest.java:23)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

This seems to be a bug, although I don't understand what causes it. Any ideas?

Artem

[Onder Kalaci]

Hi,

Thanks for reaching us.

Citus currently does not support prepared statements with parameters. And, JDBC connection, by default uses prepared statements internally. We are already tracking the issues in our github repo (#306, #572) and prioritized #572 for the next release.

There is also a related discussion in this users group, which considers one of the workarounds: https://groups.google.com/forum/#!topic/citus-users/3weujcKEf38

I can think of another workaround by disabling the use of prepared statements on the JDBC. As far as I remember,  you can do that by setting the JDBC protocol version to 2 in the database connection URL such as: dbc:postgresql://127.0.0.1:5432/username?protocolVersion=2 

Hope this helps,

Onder

[Artyom Bakulin]

Thank you, this was very helpful hint.

I found out that it is enough to disable prepared statements in JDBC driver.

This can be done by either calling  dataSource.setPrepareThreshold(0), or by adding ?prepareThreshold=0 to connection string.

In either case, I am able to use PreparedStatement API to set query parameters (which is much safer than building SQL string by hand), and yet keep my INSERTs working.

I will continue my exepriments and probably come back soon. Thank you.

5 июля 2016 г. 9:42 пользователь "Onder Kalaci" <on...@citusdata.com> написал: