Add section for PREPARE

[begriffs]

PREPARE support is one of the most frequent questions we hear from users; and I just wanted to copy/paste an example email thread from our users list here.

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

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

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.

[begriffs]

@sumedhpathak does 5.2 fix this issue?

[begriffs]

We support params in prepared statements now, but I'm not sure whether the issues was fixed of doing >6 inserts in a prepared statement. @sumedhpathak do you know?

[ozgune]

@begriffs -- We track our support for PREPARE statements in the following issue: citusdata/citus#306

In particular, this comment has a table that captures our PREPARE support matrix.

We're also planning to offer a workaround in v6.1 that mitigates issues related to PREPARE: citusdata/citus#904

[begriffs]

Nowadays perhaps the only restrictions on PREPARE are for sql functions? At least it looks that way from citusdata/citus#1179

Is this enough of a problem that we need documentation around it?

[begriffs]

We support prepare now.