impossibl / pgjdbc-ng Goto Github PK

It would be nice to have checkstyle integrated into the project in order to have a consistent style, which would make it easier to contribute.

Especially if the TABs are replaced with SPACEs instead.

Netty 4 is the active branch in the Netty community, so ideally the project should make use of that version.

Maybe @normanmaurer can help... :)

Anybody else seeing these?

testTextCodecs[test-time](com.impossibl.postgres.jdbc.CodecTest)  Time elapsed: 0.039 sec  <<< FAILURE!
java.lang.AssertionError: expected:<09:30:30> but was:<01:30:30>
    at org.junit.Assert.fail(Assert.java:88)
    at org.junit.Assert.failNotEquals(Assert.java:743)
    at org.junit.Assert.assertEquals(Assert.java:118)
    at org.junit.Assert.assertEquals(Assert.java:144)
    at com.impossibl.postgres.jdbc.CodecTest.test(CodecTest.java:320)
    at com.impossibl.postgres.jdbc.CodecTest.testTextCodecs(CodecTest.java:157)

testTextCodecs[test-time[]](com.impossibl.postgres.jdbc.CodecTest)  Time elapsed: 0.034 sec  <<< FAILURE!
java.lang.AssertionError: expected:<09:30:30> but was:<01:30:30>
    at org.junit.Assert.fail(Assert.java:88)
    at org.junit.Assert.failNotEquals(Assert.java:743)
    at org.junit.Assert.assertEquals(Assert.java:118)
    at org.junit.Assert.assertEquals(Assert.java:144)
    at com.impossibl.postgres.jdbc.CodecTest.test(CodecTest.java:320)
    at com.impossibl.postgres.jdbc.CodecTest.test(CodecTest.java:290)
    at com.impossibl.postgres.jdbc.CodecTest.testTextCodecs(CodecTest.java:157)

testTextCodecs[test-timestruct](com.impossibl.postgres.jdbc.CodecTest)  Time elapsed: 0.039 sec  <<< FAILURE!
java.lang.AssertionError: expected:<09:30:30> but was:<01:30:30>
    at org.junit.Assert.fail(Assert.java:88)
    at org.junit.Assert.failNotEquals(Assert.java:743)
    at org.junit.Assert.assertEquals(Assert.java:118)
    at org.junit.Assert.assertEquals(Assert.java:144)
    at com.impossibl.postgres.jdbc.CodecTest.test(CodecTest.java:320)
    at com.impossibl.postgres.jdbc.CodecTest.test(CodecTest.java:303)
    at com.impossibl.postgres.jdbc.CodecTest.testTextCodecs(CodecTest.java:157)

With the following connection string:

jdbc:pgsql://localhost:6432/test?preparedStatementCacheSize=0

I get:

java.lang.ClassCastException: Cannot cast java.lang.String to java.lang.Integer
at java.lang.Class.cast(Class.java:3094)
    at com.impossibl.postgres.system.BasicContext.getSetting(BasicContext.java:176)
    at com.impossibl.postgres.jdbc.PGConnectionImpl.<init>(PGConnectionImpl.java:185)
    at com.impossibl.postgres.jdbc.ConnectionUtil.createConnection(ConnectionUtil.java:168)
    at com.impossibl.postgres.jdbc.PGDriver.connect(PGDriver.java:60)
    at com.impossibl.postgres.jdbc.PGDriver.connect(PGDriver.java:50)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at org.sql2o.GenericDatasource.getConnection(GenericDatasource.java:48)
    at org.sql2o.Connection.createConnection(Connection.java:240)

Context: I am trying to use this driver with pgbouncer with pool_mode=statement

When I run the maven build on MacOS X (Mavericks), Java 7, I am getting this failure:

ests run: 3, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.311 sec <<< FAILURE!
testConnect(com.impossibl.postgres.jdbc.DriverTest) Time elapsed: 0.131 sec <<< ERROR!
java.nio.channels.UnresolvedAddressException: null
at sun.nio.ch.Net.checkAddress(Net.java:127)
at sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:640)
at org.jboss.netty.channel.socket.nio.NioClientSocketPipelineSink.connect(NioClientSocketPipelineSink.java:106)
at org.jboss.netty.channel.socket.nio.NioClientSocketPipelineSink.eventSunk(NioClientSocketPipelineSink.java:69)
at org.jboss.netty.channel.Channels.connect(Channels.java:634)
at org.jboss.netty.channel.AbstractChannel.connect(AbstractChannel.java:207)
at org.jboss.netty.bootstrap.ClientBootstrap.connect(ClientBootstrap.java:229)
at org.jboss.netty.bootstrap.ClientBootstrap.connect(ClientBootstrap.java:182)
at com.impossibl.postgres.protocol.v30.ProtocolFactoryImpl.connect(ProtocolFactoryImpl.java:92)
at com.impossibl.postgres.protocol.v30.ProtocolFactoryImpl.connect(ProtocolFactoryImpl.java:81)
at com.impossibl.postgres.system.BasicContext.(BasicContext.java:110)
at com.impossibl.postgres.jdbc.PGConnectionImpl.(PGConnectionImpl.java:159)
at com.impossibl.postgres.jdbc.PGDriver.connect(PGDriver.java:133)
at com.impossibl.postgres.jdbc.PGDriver.connect(PGDriver.java:62)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.impossibl.postgres.jdbc.DriverTest.testConnect(DriverTest.java:110)

The test seems to want to assert that getConnection() returns null, but an exception is being thrown instead (and not caught).

Hi,

I would like to know how to turn on the ssl connection. I tried:

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl", "true");
props.setProperty("sslfactory", "com.impossibl.postgres.protocol.ssl.SSLEngineFactory");
connectionFactory = new DriverManagerConnectionFactory("jdbc:" + url + ":" + port + "/" + dbname, props);

and

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("sslfactory", "com.impossibl.postgres.protocol.ssl.SSLEngineFactory");
connectionFactory = new DriverManagerConnectionFactory("jdbc:" + url + ":" + port + "/" + dbname +"?ssl=true", props);

and

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("sslfactory", "com.impossibl.postgres.protocol.ssl.NonValidatingTrustManager");
connectionFactory = new DriverManagerConnectionFactory("jdbc:" + url + ":" + port + "/" + dbname +"?ssl=true", props);

and

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl", "true");
props.setProperty("sslfactory", "com.impossibl.postgres.protocol.ssl.NonValidatingTrustManager");
connectionFactory = new DriverManagerConnectionFactory("jdbc:" + url + ":" + port + "/" + dbname, props);

There is some text that it should work with standard jdbc driver so I tried even:

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("ssl", "true");
props.setProperty("sslfactory", "org.postgresql.ssl.NonValidatingFactory");
connectionFactory = new DriverManagerConnectionFactory("jdbc:" + url + ":" + port + "/" + dbname, props);

I think this is an issue or lack of documentation :))

Kryszczyn

Every major database vendor JDBC driver provides a prepared statement cache...except the classic PostgreSQL driver. For example:

MySQL's DataSource. Defaults to caching 25 statements.

MysqlDataSource.setPrepStmtCacheSize()

Oracle has both implicit and explicit caching (see link).

OracleDatasource.setMaxStatements();

DB2.

"maxStatements specifies the maximum number of statements that
 can be kept open by the driver in an internal statement cache"

SQL Server via jTDS:

cacheMetaData (default - false)

As one of the authors of the relatively new HikariCP connection pool, we took the decision not to implement statement caching in the pool because it is so widely implemented by JDBC drivers. Not only does it add complexity to the pool, but JDBC drivers are actually in a better position to judge for a given database, given configuration, and given statement whether to cache or not cache.

In light of this, I think it would be great of pgjdbc-ng made up for the failings of "classic" PostgreSQL driver by offering prepared statement caching.

I am willing to implement, if pgjdbc-ng agrees in principal that it is useful.

Hi Kevin,

It seems that issues and pull requests are piling up as of late. An engineer at my company also found issues with the XA support that we'd like to contribute fixes for. You are somewhat familiar with my work through some earlier contributions. If you wouldn't mind adding me as project contributor, I'll be happy to make what headway I can reviewing and integrating pull requests and fixing bugs.

This project looks very interesting. Would you consider licensing it under Apache License, Version 2.0 (ASL2)? https://www.apache.org/licenses/LICENSE-2.0

We have a basic JDBC driver in Presto (https://github.com/facebook/presto) and would be interested in collaborating here, but Presto is licensed under ASL2.

Hello, I'm having the following experiences calling a function...
This function has all the "IN" parameters first... and the call from java works ok..

CREATE OR REPLACE FUNCTION prueba.working(IN in_param_0 character varying, 
OUT out_param_1 integer, OUT out_param_2 retorno_type)
  RETURNS record AS
$BODY$

    begin
        out_param_2.codigo = '00000';
        out_param_2.mensaje = 'Este es un mensaje';
        out_param_1 = 1024;
    end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

public static void working() throws SQLException, Exception {
        Connection conn = TestUtil.openDB();
        String sql = "{call working(???)}";
        CallableStatement call = conn.prepareCall(sql);
        call.setObject(1, "971238007", Types.VARCHAR);
        call.registerOutParameter(2, Types.INTEGER); 
        call.registerOutParameter(3, Types.STRUCT); 
        try {
            call.execute();
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        System.out.println("... ... ... ... ... Llamada exitosa...");
        call.close();
        conn.close();

    }

My result: ... ... ... ... ... Llamada exitosa...

But, in the next example my call from java is not working... The only change is the order of the parameters...

CREATE OR REPLACE FUNCTION prueba.not_working(OUT out_param_1 integer,
OUT out_param_2 retorno_type, IN in_param_0 character varying)
  RETURNS record AS
$BODY$

    begin
        out_param_2.codigo = '00000';
        out_param_2.mensaje = 'Este es un mensaje';
        out_param_1 = 1024;
    end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

This is how I call..

public static void notWorking() throws SQLException, Exception {
        Connection conn = TestUtil.openDB();
        String sql = "{call not_working(???)}";
        CallableStatement call = conn.prepareCall(sql);
        call.registerOutParameter(1, Types.INTEGER);
        call.registerOutParameter(2, Types.STRUCT);
        call.setObject(3, "971238007", Types.VARCHAR);
        try {
            call.execute();
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        System.out.println("... ... ... ... ... Llamada exitosa...");
        call.close();
        conn.close();
    }

I get an exception:
com.impossibl.postgres.jdbc.PGSQLSimpleException: no se pudo determinar el tipo del parámetro $1
at com.impossibl.postgres.jdbc.ErrorUtils.makeSQLException(ErrorUtils.java:159)
at com.impossibl.postgres.jdbc.ErrorUtils.makeSQLException(ErrorUtils.java:136)
at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:432)
at com.impossibl.postgres.jdbc.PGPreparedStatement$1.call(PGPreparedStatement.java:183)
at com.impossibl.postgres.jdbc.PGPreparedStatement$1.call(PGPreparedStatement.java:174)
at com.impossibl.postgres.jdbc.PGConnectionImpl.getCachedStatement(PGConnectionImpl.java:1257)
at com.impossibl.postgres.jdbc.PGPreparedStatement.parseIfNeeded(PGPreparedStatement.java:174)
at com.impossibl.postgres.jdbc.PGCallableStatement.parseIfNeeded(PGCallableStatement.java:139)
at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:210)
at com.impossibl.postgres.jdbc.PGCallableStatement.execute(PGCallableStatement.java:145)
at py.com.personal.PGDriver.ProvisionarTest.notWorking(ProvisionarTest.java:186)
at py.com.personal.PGDriver.Main.main(Main.java:31)
I also can't call to this one that has IN, OUT, OUT, IN order...

CREATE OR REPLACE FUNCTION prueba.not_working_2(IN in_param_1 character varying, 
OUT out_param_1 integer, OUT out_param_2 retorno_type, IN in_param_2 character varying)
  RETURNS record AS
$BODY$

    begin
        out_param_2.codigo = '00000';
        out_param_2.mensaje = 'Este es un mensaje';
        out_param_1 = 1024;
    end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

public static void notWorking2() throws SQLException, Exception {
        Connection conn = TestUtil.openDB();
        String sql = "{call not_working_2(????)}";
        CallableStatement call = conn.prepareCall(sql);
        call.setObject(1, "971238007", Types.VARCHAR);
        call.registerOutParameter(2, Types.INTEGER);
        call.registerOutParameter(3, Types.STRUCT); 
        call.setObject(4, "971238007", Types.VARCHAR);
        try {
            call.execute();
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        System.out.println("... ... ... ... ... Llamada exitosa...");
        call.close();
        conn.close();

    }

com.impossibl.postgres.jdbc.PGSQLSimpleException: error de sintaxis en o cerca de «$4»
at com.impossibl.postgres.jdbc.ErrorUtils.makeSQLException(ErrorUtils.java:159)
at com.impossibl.postgres.jdbc.ErrorUtils.makeSQLException(ErrorUtils.java:136)
at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:432)
at com.impossibl.postgres.jdbc.PGPreparedStatement$1.call(PGPreparedStatement.java:183)
at com.impossibl.postgres.jdbc.PGPreparedStatement$1.call(PGPreparedStatement.java:174)
at com.impossibl.postgres.jdbc.PGConnectionImpl.getCachedStatement(PGConnectionImpl.java:1257)
at com.impossibl.postgres.jdbc.PGPreparedStatement.parseIfNeeded(PGPreparedStatement.java:174)
at com.impossibl.postgres.jdbc.PGCallableStatement.parseIfNeeded(PGCallableStatement.java:139)
at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:210)
at com.impossibl.postgres.jdbc.PGCallableStatement.execute(PGCallableStatement.java:145)
at py.com.personal.PGDriver.ProvisionarTest.notWorking2(ProvisionarTest.java:205)
at py.com.personal.PGDriver.Main.main(Main.java:34)

Is there any restriction about the order of the parameters? The "IN" params should always come first? Or I'm missing something?

When I shutdown my postgres server, and then try to close my application, the driver hangs trying to close connections.

Thread Thread-2
owns: ProtocolImpl (id=122)
owns: Object (id=123)
owns: ResourceLoader (id=124)
owns: BitronixTransactionManager (id=125)
waiting for: CloseCommandImpl$1 (id=126)
Object.wait(long) line: not available [native method]
CloseCommandImpl(CommandImpl).waitFor(ProtocolListener) line: 120
CloseCommandImpl.execute(ProtocolImpl) line: 104
ProtocolImpl.execute(Command) line: 365
PGConnectionImpl.execute(Command, boolean) line: 364
PGStatement.dispose(PGConnectionImpl, ServerObjectType, String) line: 184
PGPreparedStatement(PGStatement).internalClose() line: 286
PGPreparedStatement.internalClose() line: 148
PGConnectionImpl.closeStatements(List<WeakReference>) line: 312
PGConnectionImpl.closeStatements() line: 329
PGConnectionImpl.internalClose() line: 554
PGConnectionImpl.close() line: 1107
PGXAConnection(PGPooledConnection).close() line: 127
JdbcPooledConnection.close() line: 157
XAPool.close() line: 121
PoolingDataSource.close() line: 349
ResourceLoader.shutdown() line: 93
BitronixTransactionManager.shutdown() line: 358
Activator.stop(BundleContext) line: 124
BundleContextImpl$2.run() line: 771
AccessController.doPrivileged(PrivilegedExceptionAction) line: not available [native method]
BundleContextImpl.stop() line: 764
BundleHost.stopWorker(int) line: 510
BundleHost(AbstractBundle).stop(int) line: 465
BundleHost(AbstractBundle).stop() line: 457
CrateStarterElf.stopBundles(BundleContext, Crate...) line: 368
ZApplication.stop() line: 237
ZApplication$2.run() line: 330

If one has a BigDecimal(0) this loop in Numerics.encodeFromString():210 will never end:

//Swallow leading zeros
while (numChars[ch] == '0')
  ch++;

resulting in ArrayIndexOutOfBoundsException: 1 on next iteration

This test shows the problem:

@RunWith(JUnit4.class)
public class NumericsTest {

  private Connection conn;

  @Before
  public void before() throws Exception {
    conn = TestUtil.openDB();
    TestUtil.createTable(conn, "numerictest", "id NUMERIC");
    conn.setAutoCommit(false);
  }

  @After
  public void after() throws SQLException {
    conn.setAutoCommit(true);
    TestUtil.dropTable(conn, "numerictest");
    TestUtil.closeDB(conn);
  }

  @Test
  public void testSetZero() throws Exception {

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO numerictest(id) VALUES (?)");

    pstmt.setBigDecimal(1, new BigDecimal("0"));
    pstmt.executeUpdate();

    pstmt.close();
  }

}

java.lang.ArrayIndexOutOfBoundsException: 1
    at com.impossibl.postgres.system.procs.Numerics.encodeFromString(Numerics.java:210)
    at com.impossibl.postgres.system.procs.Numerics.access$100(Numerics.java:42)
    at com.impossibl.postgres.system.procs.Numerics$BinEncoder.encode(Numerics.java:133)
    at com.impossibl.postgres.system.procs.BinaryEncoder.length(BinaryEncoder.java:51)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.lengthOfParams(ProtocolImpl.java:723)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.writeBind(ProtocolImpl.java:507)
    at com.impossibl.postgres.protocol.v30.BindExecCommandImpl.execute(BindExecCommandImpl.java:319)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.execute(ProtocolImpl.java:369)
    at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:396)
    at com.impossibl.postgres.jdbc.PGStatement.executeStatement(PGStatement.java:368)
    at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:205)
    at com.impossibl.postgres.jdbc.PGPreparedStatement.executeUpdate(PGPreparedStatement.java:229)
    at com.impossibl.postgres.jdbc.NumericsTest.testSetZero(NumericsTest.java:91)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:202)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:65)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

Under JPA 2.0 one should be able to do this:

@Entity
public class Credential {

    @Id
    private Long id;

    @Column( length = 8 )
    private byte[] salt;

    @Column( length = 32 )
    private byte[] hash;

If you use this driver you will get an exception like this:

Caused by: java.lang.ClassCastException: [B cannot be cast to java.io.InputStream
at com.impossibl.postgres.system.procs.Bytes$BinEncoder.length(Bytes.java:169)
at com.impossibl.postgres.protocol.v30.ProtocolImpl.lengthOfParams(ProtocolImpl.java:723)
at com.impossibl.postgres.protocol.v30.ProtocolImpl.writeBind(ProtocolImpl.java:507)
at com.impossibl.postgres.protocol.v30.BindExecCommandImpl.execute(BindExecCommandImpl.java:319)
at com.impossibl.postgres.protocol.v30.ProtocolImpl.execute(ProtocolImpl.java:369)
at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:428)
at com.impossibl.postgres.jdbc.PGPreparedStatement.executeBatch(PGPreparedStatement.java:328)

I am using Spring + Hibernate for my JPA implementation. I tested this against 0.30 (the latest in maven at the time of this writing).

I use 0.0.3-SNAPSHOT 5d0f7e9

The C-style comment in the query below which makes it error out:

select coalesce(h.company_id, expense.company_id) as company_id
    , coalesce(h.companyname, expense.companyname) as companyname
    , coalesce(h.project_id, expense.project_id) as project_id
    , coalesce(h.projectname, expense.projectname) as projectname
    , coalesce(h.company_is_active, expense.company_is_active) as company_is_active
    , coalesce(h.project_is_active, expense.project_is_active) as project_is_active

/*
    , coalesce(h.company_favourite_priority, expense.company_favourite_priority) as company_favourite_priority
    , coalesce(h.project_favourite_priority, expense.project_favourite_priority) as project_favourite_priority
*/


    , h.work_time
    , h.invoiceable_time

java.lang.StackOverflowError
at java.util.regex.Pattern$GroupHead.match(Pattern.java:4554)
at java.util.regex.Pattern$Loop.match(Pattern.java:4683)
at java.util.regex.Pattern$GroupTail.match(Pattern.java:4615)
at java.util.regex.Pattern$BranchConn.match(Pattern.java:4466)
..
..

If I remove the comment the query works fine

We need a clever strategy for automatically testing the driver against different server versions and configurations (e.g. SSL). Maybe something already exists somewhere?

Immediately VM's come to mind. Maybe a Maven plugin or ANT task can loop through a set of target VM's and run all tests against each.

Between unit tests I blow away my database data using TRUNCATE statements. These statements fail with this driver.

Try this:

            try ( Connection con = dataSource.getConnection() ) {
                try ( Statement st = con.createStatement() ) {
                    try ( ResultSet results = st.executeQuery( SELECT_TABLES_SQL ) ) {

                        StringBuilder builder = new StringBuilder( 256 );
                        while ( results.next() ) {
                            if ( builder.length() > 0  )
                                builder.append( ',' );

                            builder.append( results.getString( 2 ) );
                        }

                        try ( Statement truncate = con.createStatement() ) {
                            truncate.execute( "TRUNCATE " + builder.toString() + " CASCADE" );
                        }
                    }
                }

                con.commit();
            }

Hi,
You did a great job on pgjdbc-ng, but is there any query logging feature available ? I didn't find any information anywhere.

http://impossibl.github.io/pgjdbc-ng/apidocs/0.3/index.html gives me a 404 error.

Thanks

Hi,

Could we discuss the plan to release 0.4? A couple of important bugs have been fixed and it would be nice to release something.

My 2 cents: I would like to have this fixed before the release:

• commit gmokki@c5d2c32 and gmokki@56f4729 from the PR #73
  -> I can merge them/update them and create a new PR with only these commits updated to work with current develop branch.
• commit: #84
• I was wondering if we should update the Guava classes. I have something for all the classes except the *Streams classes. Did you clean unused methods in these classes to reduce the dependency to other Guava classes or did the Guava guys added a lot of stuff?

See if we can fix:
#65
#81

Thanks for your feedback.

The following test fails:

    public void testSetIntegerFails() throws Exception {
        String sql = "SELECT ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 1);
        ps.executeQuery();
    }

Some ORMs generate queries like this:
SELECT ..... WHERE a.id = $1 AND EXISTS (SELECT $2 FROM my_entity WHERE ...)
and then issue a "ps.setInt(2, 1)"

This works fine with the old, official, driver but fails with pgjdbc-ng which makes using this driver with ORMs difficult.

Thanks.

This may (or may not) be related to the XA DataSource work on jesperpederson's ds branch...

Following an unclean shutdown of my application (I had to kill it), my XA Transaction Manager (Bitronix) tries to rollback transactions that are uncommitted in its logs ... this is causing an infinite wait during startup. I am pretty sure that Bitronix is "correct" in its behavior, both because I have worked on it, and because it is a very mature transaction manager over 8 years old supporting a wide array of databases.

Here is the stacktrace of the hang:

"main" prio=5 tid=0x00007f9b6100b000 nid=0x80b in Object.wait() [0x00000001088a4000]
java.lang.Thread.State: TIMED_WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
- waiting on <0x000000012ae73150> (a com.impossibl.postgres.protocol.v30.BindExecCommandImpl$BindExecCommandListener)
at com.impossibl.postgres.protocol.v30.CommandImpl.waitFor(CommandImpl.java:120)
- locked <0x000000012ae73150> (a com.impossibl.postgres.protocol.v30.BindExecCommandImpl$BindExecCommandListener)
at com.impossibl.postgres.protocol.v30.BindExecCommandImpl.execute(BindExecCommandImpl.java:337)
at com.impossibl.postgres.protocol.v30.ProtocolImpl.execute(ProtocolImpl.java:365)
- locked <0x00000001322f3d40> (a com.impossibl.postgres.protocol.v30.ProtocolImpl)
at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:364)
at com.impossibl.postgres.jdbc.PGStatement.executeStatement(PGStatement.java:366)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:105)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:124)
at com.impossibl.postgres.jdbc.PGSimpleStatement.executeUpdate(PGSimpleStatement.java:185)
at com.impossibl.postgres.jdbc.xa.PGXAConnection.rollback(PGXAConnection.java:363)
at bitronix.tm.recovery.RecoveryHelper.rollback(RecoveryHelper.java:215)
at bitronix.tm.recovery.Recoverer.rollback(Recoverer.java:474)
at bitronix.tm.recovery.Recoverer.rollbackAbortedBranchesOfResource(Recoverer.java:450)
at bitronix.tm.recovery.Recoverer.rollbackAbortedTransactions(Recoverer.java:415)
at bitronix.tm.recovery.Recoverer.run(Recoverer.java:150)
at bitronix.tm.BitronixTransactionManager.(BitronixTransactionManager.java:85)
at bitronix.tm.TransactionManagerServices.getTransactionManager(TransactionManagerServices.java:70)
at bitronix.tm.osgi.Activator.start(Activator.java:97)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:300)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:292)
at org.ziptie.crates.CrateStarterElf.start(CrateStarterElf.java:282)
at org.ziptie.crates.CrateStarterElf.activateBundles(CrateStarterElf.java:193)
at org.ziptie.crates.CrateStarterElf.activateBundles(CrateStarterElf.java:219)
at org.ziptie.zap.bootstrap.ZApplication.start(ZApplication.java:193)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:353)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:180)
at org.eclipse.core.runtime.adaptor.EclipseStarter.main(EclipseStarter.java:151)

I apologize for asking a question here, but I couldn't find any mailing list or other place to do so.

I came across pgjdbc-ng referenced from HikariCP, and it looks great. I see that your interface to the server is Netty-based.

Given that, is there any plans for / interest in a non-JDBC async API that would allow users to better leverage the async nature of the underlying driver?

The current driver only support 2BG Blobs (as that was the limit until 9.3).

Hi, I have not checked it practically, but looking on the code I got an impression, that namespace resolution for types is not really done (for example cache lookups per object name).

I also have not seen any test cases, that would cover working with objects in different schemas.

(For example standard PostgreSQL JDBC driver does not support namespaces correctly as well)

With non standard types (e.g hstore, inet, long array), I got a NullPointerException when using batched prepared statement.

public void testInet() throws SQLException {
    try (Connection conn = DriverManager.getConnection(dbURL, username, password);
        Statement s = conn.createStatement();
        PreparedStatement ps = conn.prepareStatement("insert into testinet values (?)");
        ResultSet rs = s.executeQuery("select ip from testinet")) {
      while (rs.next()) {
        System.out.println(rs.getObject(1) + " - " + rs.getObject(1).getClass());
      }
      ps.setObject(1, new InetAddr("192.168.2.1"));
      ps.addBatch();
      ps.executeBatch();
      // ps.executeUpdate();
      try (ResultSet rs1 = s.executeQuery("select ip from testinet")) {
        while (rs1.next()) {
          System.out.println(rs1.getObject(1) + " - " + rs1.getObject(1).getClass());
        }
      }
    }
  }

Exception in thread "main" java.lang.NullPointerException
    at com.impossibl.postgres.jdbc.SQLTypeUtils.mapSetType(SQLTypeUtils.java:84)
    at com.impossibl.postgres.jdbc.PGPreparedStatement.coerceParameters(PGPreparedStatement.java:213)
    at com.impossibl.postgres.jdbc.PGPreparedStatement.addBatch(PGPreparedStatement.java:277)

It works fine with PreparedStatement.executeUpdate().

Modifying com.impossibl.postgres.jdbc.PGPreparedStatement.coerceParameters() test seems to fix the issue, but I am not sure it is the proper fix.

private void coerceParameters() throws SQLException {

    for (int c = 0, sz = parameterTypes.size(); c < sz; ++c) {

      Type parameterType = parameterTypes.get(c);
      Object parameterValue = parameterValues.get(c);
      if (parameterValue != null && parameterType != null) { // <--- check on parameterType added
        Class<?> targetType = mapSetType(parameterType);

        try {
          parameterValue = coerce(parameterValue, parameterType, targetType, Collections.<String, Class<?>>emptyMap(), TimeZone.getDefault(), connection);
        }
        catch (SQLException coercionException) {
          throw new SQLException("Error converting parameter " + c, coercionException);
        }
      }

      parameterValues.set(c, parameterValue);
    }

  }

During testing of the XA DataSource, after an unclean shutdown, upon startup I encounter this error. I don't think it is related to the XA DataSource in general. This error was no encountered with the old "official" pgjdbc driver.

Dec 02, 2013 1:56:10 AM com.impossibl.postgres.jdbc.ThreadedHousekeeper$HousekeeperReference cleanup
WARNING: cleaning up leaked result-set
java.lang.Exception
at com.impossibl.postgres.jdbc.PGResultSet$Cleanup.(PGResultSet.java:127)
at com.impossibl.postgres.jdbc.PGResultSet.(PGResultSet.java:200)
at com.impossibl.postgres.jdbc.PGResultSet.(PGResultSet.java:183)
at com.impossibl.postgres.jdbc.PGStatement.createResultSet(PGStatement.java:380)
at com.impossibl.postgres.jdbc.PGStatement.createResultSet(PGStatement.java:375)
at com.impossibl.postgres.jdbc.PGStatement.getGeneratedKeys(PGStatement.java:611)
at com.impossibl.postgres.jdbc.PGStatementDelegator.getGeneratedKeys(PGStatementDelegator.java:191)
at com.impossibl.postgres.jdbc.PGStatementDelegator.getGeneratedKeys(PGStatementDelegator.java:191)
at bitronix.tm.resource.jdbc.proxy.PreparedStatementJavaProxy.getGeneratedKeys(PreparedStatementJavaProxy.java:110)
at org.sansorm.internal.OrmWriter.setParamsExecuteClose(OrmWriter.java:353)
at org.sansorm.internal.OrmWriter.updateObject(OrmWriter.java:203)
at org.sansorm.OrmElf.updateObject(OrmElf.java:279)
at org.ziptie.provider.compliance.ComplianceProvider$6.execute(ComplianceProvider.java:413)
at org.ziptie.provider.compliance.ComplianceProvider$6.execute(ComplianceProvider.java:400)
at org.sansorm.SqlClosure.execute(SqlClosure.java:130)
at org.ziptie.provider.compliance.ComplianceProvider.updateRuleSet(ComplianceProvider.java:399)
at org.ziptie.provider.compliance.ComplianceProvider.saveRuleSet(ComplianceProvider.java:56)
at org.ziptie.provider.compliance.internal.ComplianceActivator.scanPolicies(ComplianceActivator.java:101)
at org.ziptie.provider.compliance.internal.ComplianceActivator.start(ComplianceActivator.java:50)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:300)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.start(AbstractBundle.java:292)
at org.ziptie.crates.CrateStarterElf.start(CrateStarterElf.java:282)
at org.ziptie.crates.CrateStarterElf.activateBundles(CrateStarterElf.java:193)
at org.ziptie.crates.CrateStarterElf.activateBundles(CrateStarterElf.java:219)
at org.ziptie.zap.bootstrap.ZApplication.start(ZApplication.java:193)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:353)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:180)
at org.eclipse.core.runtime.adaptor.EclipseStarter.main(EclipseStarter.java:151)

The housekeeper should be configured using a parameter in the connection url, such it can be enabled or disabled on a case-by-case basis.

Furthermore, it shouldn't be static, since that shares it across all deployments.

Alternative, configuration through a system property - default is disabled.

I experience that retrieving and storing large Blobs using this driver is quite slow, especially compared to using lo_import in psql (http://www.postgresql.org/docs/9.3/static/lo-funcs.html). Is it possible to increase the buffer or use some other server-side functions to speed up things?

private static final Pattern URL_PATTERN =
  Pattern.compile("jdbc:postgresql:(?://((?:[a-zA-Z0-9\\-\\.]+|\\[[0-9a-f\\:]+\\])(?:\\:(?:\\d+))?(?:,(?:[a-zA-Z0-9\\-\\.]+|\\[[0-9a-f\\:]+\\])(?:\\:(?:\\d+))?)*)/)?(\\w+)(?:[\\?\\&](.*))?");
 Matcher urlMatcher = URL_PATTERN.matcher("jdbc:postgresql://localhost:5432/db-withdash");
  System.out.println(urlMatcher.matches());

returns false.

The following pattern fixes this:

private static final Pattern URL_PATTERN =
      Pattern.compile("jdbc:postgresql:(?://((?:[a-zA-Z0-9\\-\\.]+|\\[[0-9a-f\\:]+\\])(?:\\:(?:\\d+))?(?:,(?:[a-zA-Z0-9\\-\\.]+|\\[[0-9a-f\\:]+\\])(?:\\:(?:\\d+))?)*)/)?((?:\\w|-)+)(?:[\\?\\&](.*))?");

I have a blob mapped to a column of type OID and when persisting using Hibernate (which wraps blobs in proxies) I get this exception:

Caused by: org.hibernate.exception.GenericJDBCException: Error converting parameter 1
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy319.executeUpdate(Unknown Source)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:56)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2962)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3403)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1210)
at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:986)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:241)
at com.sun.proxy.$Proxy100.flush(Unknown Source)
at org.scala_libs.jpa.ScalaEntityManager$class.flush(ScalaEntityManager.scala:174)
at no.officenet.origo.core.infrastructure.jpa.OrigoScalaEntityManager.flush(RepositorySupport.scala:27)
at no.officenet.origo.core.infrastructure.jpa.RepositorySupport$class.flush(RepositorySupport.scala:23)
at no.officenet.origo.core.domain.service.document.DocumentRepositoryImpl.flush(DocumentRepository.scala:21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
... 235 more
Caused by: java.sql.SQLException: Error converting parameter 1
at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:179)
at com.impossibl.postgres.jdbc.PGPreparedStatement.executeUpdate(PGPreparedStatement.java:207)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 266 more
Caused by: java.sql.SQLException: Coercion from 'com.sun.proxy.$Proxy321' to 'int' is not supported
at com.impossibl.postgres.jdbc.SQLTypeUtils.createCoercionException(SQLTypeUtils.java:866)
at com.impossibl.postgres.jdbc.SQLTypeUtils.coerceToInt(SQLTypeUtils.java:259)
at com.impossibl.postgres.jdbc.SQLTypeUtils.coerce(SQLTypeUtils.java:106)
at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:176)
... 272 more

I have a fresh clone on two machines (both Macs) with Postgres 9.3.0.0 and both are failing a significant number of tests. Previously all tests passed, hence the fresh clones. I haven't updated in several weeks, but when I did (just now)...

When the build failed on the first machine, I cloned clean from git on a second machine, but the errors are the same.

This is representative of most of the errors:

testEmptyQuery(com.impossibl.postgres.jdbc.StatementTest) Time elapsed: 0.079 sec <<< ERROR!
java.sql.SQLException: Error parsing SQL at position 0
at com.impossibl.postgres.jdbc.PGConnectionImpl.parseSQL(PGConnectionImpl.java:343)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:122)
at com.impossibl.postgres.jdbc.StatementTest.testEmptyQuery(StatementTest.java:126)

testParsingSemiColons(com.impossibl.postgres.jdbc.StatementTest) Time elapsed: 0.08 sec <<< ERROR!
java.sql.SQLException: Error parsing SQL at position 155
at com.impossibl.postgres.jdbc.PGConnectionImpl.parseSQL(PGConnectionImpl.java:343)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:122)
at com.impossibl.postgres.jdbc.StatementTest.testParsingSemiColons(StatementTest.java:384)

Running com.impossibl.postgres.jdbc.StructTest
Tests run: 4, Failures: 0, Errors: 4, Skipped: 0, Time elapsed: 0.352 sec <<< FAILURE!
testResultSetTypeMap(com.impossibl.postgres.jdbc.StructTest) Time elapsed: 0.089 sec <<< ERROR!
java.sql.SQLException: Error parsing SQL at position 36
at com.impossibl.postgres.jdbc.PGConnectionImpl.parseSQL(PGConnectionImpl.java:343)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:122)
at com.impossibl.postgres.jdbc.PGSimpleStatement.executeQuery(PGSimpleStatement.java:175)
at com.impossibl.postgres.jdbc.StructTest.testResultSetTypeMap(StructTest.java:149)

testSpecificType(com.impossibl.postgres.jdbc.StructTest) Time elapsed: 0.088 sec <<< ERROR!
java.sql.SQLException: Error parsing SQL at position 36
at com.impossibl.postgres.jdbc.PGConnectionImpl.parseSQL(PGConnectionImpl.java:343)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:122)
at com.impossibl.postgres.jdbc.PGSimpleStatement.executeQuery(PGSimpleStatement.java:175)
at com.impossibl.postgres.jdbc.StructTest.testSpecificType(StructTest.java:122)

testConnectionTypeMap(com.impossibl.postgres.jdbc.StructTest) Time elapsed: 0.083 sec <<< ERROR!
java.sql.SQLException: Error parsing SQL at position 36
at com.impossibl.postgres.jdbc.PGConnectionImpl.parseSQL(PGConnectionImpl.java:343)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:122)
at com.impossibl.postgres.jdbc.PGSimpleStatement.executeQuery(PGSimpleStatement.java:175)
at com.impossibl.postgres.jdbc.StructTest.testConnectionTypeMap(StructTest.java:179)

testConnectionTypeMapFail(com.impossibl.postgres.jdbc.StructTest) Time elapsed: 0.092 sec <<< ERROR!
java.sql.SQLException: Error parsing SQL at position 36
at com.impossibl.postgres.jdbc.PGConnectionImpl.parseSQL(PGConnectionImpl.java:343)
at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:122)
at com.impossibl.postgres.jdbc.PGSimpleStatement.executeQuery(PGSimpleStatement.java:175)
at com.impossibl.postgres.jdbc.StructTest.testConnectionTypeMapFail(StructTest.java:205)

Any ideas?

Add support for the DataSource and XADataSource interfaces.

Using setString (stmt.setString(3, "00:50:56:00:00:09");) to set a MAC throws an IOException. From the stack trace it looks like it's been converted to bytes first.
The setBytes method (stmt.setBytes(3, new byte[] {0,0x50,0x56,0,0,0x09});) works as expected.

java.sql.SQLException: java.io.IOException: invalid length
     at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:452)
     at com.impossibl.postgres.jdbc.PGStatement.executeStatement(PGStatement.java:372)
     at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:239)
     at com.impossibl.postgres.jdbc.PGPreparedStatement.executeQuery(PGPreparedStatement.java:255)
     at com.impossibl.postgres.jdbc.PGPreparedStatement.executeQuery(PGPreparedStatement.java:92)
     at com.impossibl.postgres.jdbc.PGPreparedStatementDelegator.executeQuery(PGPreparedStatementDelegator.java:134)
     at _private_
With Throwable cause: java.io.IOException: invalid length
     at com.impossibl.postgres.system.procs.MacAddrs$BinEncoder.encode(MacAddrs.java:99)
     at com.impossibl.postgres.system.procs.BinaryEncoder.length(BinaryEncoder.java:51)
     at com.impossibl.postgres.protocol.v30.ProtocolImpl.lengthOfParams(ProtocolImpl.java:723)
     at com.impossibl.postgres.protocol.v30.ProtocolImpl.writeBind(ProtocolImpl.java:507)
     at com.impossibl.postgres.protocol.v30.BindExecCommandImpl.execute(BindExecCommandImpl.java:319)
     at com.impossibl.postgres.protocol.v30.ProtocolImpl.execute(ProtocolImpl.java:369)
     at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:428)
     at com.impossibl.postgres.jdbc.PGStatement.executeStatement(PGStatement.java:372)
     at com.impossibl.postgres.jdbc.PGPreparedStatement.execute(PGPreparedStatement.java:239)
     at com.impossibl.postgres.jdbc.PGPreparedStatement.executeQuery(PGPreparedStatement.java:255)
     at com.impossibl.postgres.jdbc.PGPreparedStatement.executeQuery(PGPreparedStatement.java:92)
     at com.impossibl.postgres.jdbc.PGPreparedStatementDelegator.executeQuery(PGPreparedStatementDelegator.java:134)
    at _private_

Using current master against PostgreSQL 9.2.4 fails the BlobTest, since LargeObject64 is chosen over LargeObject.

When looking at the compatible() compare, this.major is set to 9; this.minor + this.revision are 0, hence it passes.

The method should probably be cleaned up a bit in order to verify null instances on both sides. Also if this.X takes precedence over the parameter.

public class Strings extends SimpleProcProvider {

  public static final BinDecoder BINARY_DECODER = new BinDecoder();
  public static final BinDecoder BINARY_ENCODER = new BinDecoder();

should be

public class Strings extends SimpleProcProvider {

  public static final BinDecoder BINARY_DECODER = new BinDecoder();
  public static final BinEncoder BINARY_ENCODER = new BinEncoder();

Ref the javadoc on SQLIntegrityConstraintViolationException:

/**
 * The subclass of {@link SQLException} thrown when the SQLState class value
 * is '<i>23</i>', or under vendor-specified conditions.
 * This indicates that an integrity
 * constraint (foreign key, primary key or unique key) has been violated.
 * <p>
 * Please consult your driver vendor documentation for the vendor-specified
 * conditions for which this <code>Exception</code> may be thrown.
 * @since 1.6
 */

Thanks

Need to start by just putting a translation system into place. Translations can come later.

I would like to add a mac address codec but I am not sure how to plug it to test it.
If you can give me some guidance.

Thanks,

Christophe

/**
 * Copyright (c) 2013, impossibl.com
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 *  * Redistributions of source code must retain the above copyright notice,
 *    this list of conditions and the following disclaimer.
 *  * Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in the
 *    documentation and/or other materials provided with the distribution.
 *  * Neither the name of impossibl.com nor the names of its contributors may
 *    be used to endorse or promote products derived from this software
 *    without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */
package com.impossibl.postgres.system.procs;

import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.jboss.netty.buffer.ChannelBuffer;

import com.impossibl.postgres.system.Context;
import com.impossibl.postgres.types.PrimitiveType;
import com.impossibl.postgres.types.Type;

public class MacAddrs extends SimpleProcProvider {

  // http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/utils/inet.h;h=3d8e31c31c83d5544ea170144b03b0357cd77b2b;hb=HEAD
  // http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/mac.c;h=aa9993fa5c6406fa7274ad61de270d5086781a5d;hb=HEAD
  public MacAddrs() {
    super(new TxtEncoder(), new TxtDecoder(), new BinEncoder(), new BinDecoder(), "macaddr");
  }

  static class BinDecoder extends BinaryDecoder {

    @Override
    public PrimitiveType getInputPrimitiveType() {
      return PrimitiveType.Binary;
    }

    @Override
    public Class<?> getOutputType() {
      return byte[].class;
    }

    @Override
    public byte[] decode(Type type, ChannelBuffer buffer, Context context) throws IOException {
      int length = buffer.readInt();
      if (length == -1) {
        return null;
      }
      else if (length != 6) {
        throw new IOException("invalid length");
      }
      byte[] bytes = new byte[6];
      buffer.readBytes(bytes);
      buffer.skipBytes(length - bytes.length);
      return bytes;
    }

  }

  static class BinEncoder extends BinaryEncoder {

    @Override
    public Class<?> getInputType() {
      return byte[].class;
    }

    @Override
    public PrimitiveType getOutputPrimitiveType() {
      return PrimitiveType.Binary;
    }

    @Override
    public void encode(Type type, ChannelBuffer buffer, Object val, Context context) throws IOException {
      if (val == null) {
        buffer.writeInt(-1);
      }
      else {
        byte[] bytes = (byte[]) val;
        if (bytes.length != 6) {
          throw new IOException("invalid length");
        }
        buffer.writeInt(6);
        buffer.writeBytes(bytes);
      }
    }
  }

  static class TxtDecoder extends TextDecoder {
    /*
     * '08:00:2b:01:02:03' '08-00-2b-01-02-03' '08002b:010203' '08002b-010203'
     * '0800.2b01.0203' '08002b010203'
     */
    private static final Pattern macPattern = Pattern
        .compile("([0-9a-f-A-F]{2})[:-]?([0-9a-f-A-F]{2})[-:.]?([0-9a-f-A-F]{2})[:-]?([0-9a-f-A-F]{2})[-:.]?([0-9a-f-A-F]{2})[:-]?([0-9a-f-A-F]{2})");

    @Override
    public PrimitiveType getInputPrimitiveType() {
      return PrimitiveType.Binary;
    }

    @Override
    public Class<?> getOutputType() {
      return byte[].class;
    }

    @Override
    public byte[] decode(Type type, CharSequence buffer, Context context) throws IOException {
      Matcher m = macPattern.matcher(buffer);
      if (!m.matches()) {
        throw new IOException("Invalid Mac address: " + buffer);
      }
      byte[] addr = new byte[6];
      for (int i = 0; i < 6; i++) {
        addr[i] = (byte) Integer.parseInt(m.group(i + 1), 16);
      }
      return addr;
    }

  }

  static class TxtEncoder extends TextEncoder {
    private static final char[] hexDigits = new char[] {'0', '1', '2', '3', '4', '5', '6', '6', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f'};
    private static final char separator = ':';

    @Override
    public Class<?> getInputType() {
      return byte[].class;
    }

    @Override
    public PrimitiveType getOutputPrimitiveType() {
      return PrimitiveType.Binary;
    }

    @Override
    public void encode(Type type, StringBuilder buffer, Object val, Context context) throws IOException {
      byte[] addr = (byte[]) val;
      if (addr.length != 6) {
        throw new IOException("invalid length");
      }
      for (byte b : addr) {
          int bi = b & 0xff;
          buffer.append(hexDigits[bi >> 4]);
          buffer.append(hexDigits[bi & 0xf]).append(separator);
      }
      buffer.setLength(buffer.length() - 1);
    }

  }

}

While I was running some tests on the performance of PreparedStatements, I ran across a rather severe performance issue.

Comparing to the current PostgreSQL driver...

long start = System.nanoTime();
for (int i = 0; i < 100000; i++) {
    PreparedStatement prepareStatement = connection.prepareStatement("");
    prepareStatement.close();
}
System.out.println("Prepare: " + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - start));

With the current PostgreSQL driver this loop takes 198ms on my machine. Using the pgjdbc-ng driver, the same loop takes 9872ms. Note this is with an empty statement.

When I run the query (specific to my env):

SELECT * from device WHERE hostname=? AND ip_low>0 AND ip_high<?

the performance for the PostgreSQL driver is 680ms and pgjdbc-ng is 11463ms. This is one order of magnitude difference.

Ensure all SQLExceptions thrown are of the correct derived type and carry the correct SQLState code

A transaction manager that I use (bitronix) requires a DataSource instead of a Driver, so I am unable to test pgjdbc-ng in our product yet. Another product of ours uses the HikariCP connection pool, which also only supports DataSources. java.sql.DataSource is the "preferred" way to obtain connections since Java 1.4. According to the JavaDoc for DataSource:

"An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection."

Using version 0.3

This query:

select ARRAY(select ROW(1::bigint, 'nisse'::varchar));

gives the following output in pgsql-console:

andreak=# select ARRAY(select ROW(1::bigint, 'nisse'::varchar));
     array
---------------
 {"(1,nisse)"}
(1 row)

But trying to retrieve the value from JDBC results in this exception:

java.lang.ClassCastException: com.impossibl.postgres.types.PsuedoType cannot be cast to com.impossibl.postgres.types.ArrayType
    at com.impossibl.postgres.system.procs.Arrays$BinDecoder.decode(Arrays.java:81)
    at com.impossibl.postgres.system.procs.BinaryDecoder.decode(BinaryDecoder.java:44)
    at com.impossibl.postgres.protocol.v30.BindExecCommandImpl$BindExecCommandListener.rowData(BindExecCommandImpl.java:113)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.receiveRowData(ProtocolImpl.java:999)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.dispatch(ProtocolImpl.java:789)
    at com.impossibl.postgres.protocol.v30.MessageHandler.channelRead(MessageHandler.java:46)
    at io.netty.channel.DefaultChannelHandlerContext.invokeChannelRead(DefaultChannelHandlerContext.java:338)
    at io.netty.channel.DefaultChannelHandlerContext.fireChannelRead(DefaultChannelHandlerContext.java:324)
    at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:153)
    at io.netty.channel.DefaultChannelHandlerContext.invokeChannelRead(DefaultChannelHandlerContext.java:338)
    at io.netty.channel.DefaultChannelHandlerContext.fireChannelRead(DefaultChannelHandlerContext.java:324)
    at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:785)
    at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:132)
    at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:485)
    at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:452)
    at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:346)
    at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:101)
    at java.lang.Thread.run(Thread.java:724)
    at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:275)
    at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:252)
    at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:161)
    at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:194)
    at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:148)
    at com.sun.proxy.$Proxy147.execute(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor880.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:214)
    at com.sun.proxy.$Proxy148.execute(Unknown Source)
    at com.intellij.persistence.database.console.JdbcEngine.executeQueryInner(JdbcEngine.java:379)

Any hints on how to retrieve the value from such a result (array of bigint,varchar pairs)? Can I use a custom type for this, in that case are there any examples mapping custom-types I can look at?

Thanks.

The ServerErrorTest fails with current master against PostgreSQL 9.2.4, since the fields in the SQLException are all null.

I found myself in SQLTextEscapes.java and the Node processEscape(EscapeNode escape, Context context) method...

In this method context is used in three places:

    switch(type.toString().toLowerCase()) {
      case "d":
        result = processDateEscape(escape, context);
        break;

      case "t":
        result = processTimeEscape(escape, context);
        break;

      case "ts":
        result = processTimestampEscape(escape, context);
...

However, looking into processDateEscape() et al, none of them use the Context that is passed in. It seems like it was passed in because Context has these methods:

  DateTimeFormat getDateFormatter();
  DateTimeFormat getTimeFormatter();
  DateTimeFormat getTimestampFormatter();

However, they are not used. Is this an oversight? There are several call sites passing in Context, but if it is never used it is somewhat misleading as to its purpose.

The codebase contains a bunch of code copied from the original pgjdbc driver, but the LICENSE file has been changed to say "Copyright (c) 2013, impossibl.com", instead of PostgreSQL Global Development Group. The BSD-style license allows forking and pretty much everything, but the one thing it does require is to keep the original copyright statement intact.

I have this function in postgres.

CREATE OR REPLACE FUNCTION pruebas.prueba()
  RETURNS void AS
$BODY$
begin
    update apns set descripcion = 'MODIFICADO' where apnid = '111';
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pruebas.prueba()
  OWNER TO postgres;

I want to call it from Java like this:

    public static void main(String[] args) throws Exception {
        System.out.println(" ************** Prueba Package Function ************* ");
        Connection conn = Main.openDB();
        CallableStatement call = conn.prepareCall("{call pruebas.prueba()}");
        call.execute();
        call.close();
        Main.closeDB(conn);
    }

I'm getting this exception:
************** Prueba Package Function *************
Exception in thread "main" java.sql.SQLException: Invalid escape syntax (0)
at com.impossibl.postgres.jdbc.SQLTextEscapes.checkSize(SQLTextEscapes.java:300)
at com.impossibl.postgres.jdbc.SQLTextEscapes.checkSize(SQLTextEscapes.java:289)
at com.impossibl.postgres.jdbc.SQLTextEscapes.processCallEscape(SQLTextEscapes.java:250)
at com.impossibl.postgres.jdbc.SQLTextEscapes.processEscape(SQLTextEscapes.java:111)
at com.impossibl.postgres.jdbc.SQLTextEscapes.access$000(SQLTextEscapes.java:63)
at com.impossibl.postgres.jdbc.SQLTextEscapes$1.process(SQLTextEscapes.java:76)
at com.impossibl.postgres.jdbc.SQLTextTree$CompositeNode.process(SQLTextTree.java:192)
at com.impossibl.postgres.jdbc.SQLTextTree$CompositeNode.process(SQLTextTree.java:178)
at com.impossibl.postgres.jdbc.SQLTextTree$CompositeNode.process(SQLTextTree.java:178)
at com.impossibl.postgres.jdbc.SQLText.process(SQLText.java:92)
at com.impossibl.postgres.jdbc.SQLTextEscapes.processEscapes(SQLTextEscapes.java:67)
at com.impossibl.postgres.jdbc.PGConnectionImpl.prepareCall(PGConnectionImpl.java:1048)
at com.impossibl.postgres.jdbc.PGConnectionImpl.prepareCall(PGConnectionImpl.java:1028)
at com.impossibl.postgres.jdbc.PGConnectionImpl.prepareCall(PGConnectionImpl.java:1019)
at com.impossibl.postgres.jdbc.PGConnectionImpl.prepareCall(PGConnectionImpl.java:1014)
at py.com.personal.PGDriver.Main.main(Main.java:130)

It works when I put the function in public schema, but I need it in another schema.
How can I call the function?

I'd like to implement a TransactionalBlobInputStream which takes a BlobInputStream as input and ensures there's an active transaction before calling read() etc., reconnecting and seek()-ing if necessary.

Thinking about something like this:

public TransactionalBlobInputStream(BlobInputStream bis) {
// getLargeObject doesn't exist on BlobInputStream
    this.lo = big.getLargeObject; 
}

public int read() {
// Use Spring's TransactionSynchronizationManager.isActualTransactionActive
// to test if there's an open TX and test if this.lo is useable,
//  or re-connect and create new LargeObject if necessary
}

How about making more stuff public/protected so one may override stuff and extend it?

Thanks

Get this exception:
java.lang.NumberFormatException: For input string: "4beta1"

But the exception is masked in IOException:
ProtocolFactoryImpl:288
io = new IOException(e.getCause());

The real NFE-stack-trace is:

java.lang.NumberFormatException: For input string: "4beta1"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.lang.Integer.parseInt(Integer.java:492)
    at java.lang.Integer.valueOf(Integer.java:582)
    at com.impossibl.postgres.system.Version.parsePart(Version.java:59)
    at com.impossibl.postgres.system.Version.parse(Version.java:45)
    at com.impossibl.postgres.system.BasicContext.updateSystemParameter(BasicContext.java:579)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.receiveParameterStatus(ProtocolImpl.java:1158)
    at com.impossibl.postgres.protocol.v30.ProtocolImpl.dispatch(ProtocolImpl.java:837)
    at com.impossibl.postgres.protocol.v30.MessageHandler.channelRead(MessageHandler.java:46)
    at io.netty.channel.DefaultChannelHandlerContext.invokeChannelRead(DefaultChannelHandlerContext.java:338)
    at io.netty.channel.DefaultChannelHandlerContext.fireChannelRead(DefaultChannelHandlerContext.java:324)
    at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:153)
    at io.netty.channel.DefaultChannelHandlerContext.invokeChannelRead(DefaultChannelHandlerContext.java:338)
    at io.netty.channel.DefaultChannelHandlerContext.fireChannelRead(DefaultChannelHandlerContext.java:324)
    at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:785)
    at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:132)
    at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:485)
    at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:452)
    at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:346)
    at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:101)
    at java.lang.Thread.run(Thread.java:745)

But the Exception in the logs is:

java.io.IOException
    at com.impossibl.postgres.protocol.v30.ProtocolFactoryImpl.translateConnectionException(ProtocolFactoryImpl.java:288)
    at com.impossibl.postgres.protocol.v30.ProtocolFactoryImpl.connect(ProtocolFactoryImpl.java:199)
    at com.impossibl.postgres.protocol.v30.ProtocolFactoryImpl.connect(ProtocolFactoryImpl.java:90)
    at com.impossibl.postgres.system.BasicContext.<init>(BasicContext.java:130)
    at com.impossibl.postgres.jdbc.PGConnectionImpl.<init>(PGConnectionImpl.java:181)
    at com.impossibl.postgres.jdbc.ConnectionUtil.createConnection(ConnectionUtil.java:168)
    at com.impossibl.postgres.jdbc.PGDriver.connect(PGDriver.java:60)
    at com.impossibl.postgres.jdbc.PGDriver.connect(PGDriver.java:50)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)

The current implementation reads the entire stream into memory (ByteOutputStream in the case of setBinaryStream()), this will never scale for data that is tens or hundreds of megabytes or multiple-gigabytes in size.

Data should be streamed when the transaction is committed.

The official driver has this now:
pgjdbc/pgjdbc@e9ac5f8

It would be great if NG has it too.