Hello, I read the JDBC driver's source code, documentation, and the project web site to compile the missing features and wanted improvements. Attached file is the list of things I noticed. Some of the items may be on the project web site. I hope this will be a bit useful. The driver version I investigated is postgresql-jdbc-9.0-801. I'm interested in discussing which features can be feasible with the current PostgreSQL server and which features require new functionality of the server. However, as I'm relatively new to PostgreSQL, I may fail to keep up with your level of discussions. Regards MauMau Missing features and wanted improvements of PostgreSQL JDBC driver Table of contents 1. Missing features of JDBC specification(big features) 2. Missing features of JDBC specification(small features) 3. List of unsupported methods 4. Regression test 1. Missing features of JDBC specification(big features) I gathered the following features from java.sql package's Javadoc top page [JDBC 2.1 Core] Custom mapping of user-defined types (UDTs) Data types--interfaces mapping the SQL3 data types: Ref, Struct [JDBC 2.1 Standard Extension, javax.sql package] RowSet technology [JDBC 3.0] Statement pooling Ability to identify parameters to CallableStatement objects by name as well as by index ResultSet holdability -- ability to specify whether cursors should be held open or closed at the end of a transaction Ability to retrieve and update the SQL structured type instance that a Ref object references Ability to programmatically update CLOB and REF values Support of java.sql.Types.DATALINK Metadata for retrieving SQL type hierarchies [JDBC 4.0] National Character Set support including NClob data type SQLException enhancements -- Added support for cause chaining; New SQLExceptions added for common SQLState class value codes Enhanced Blob/Clob functionality -- Support provided to create and free a Blob/Clob instance as well as additional methods added to improve accessiblity (e.g. Connection interface's createBlob()/createClob()) Support for accessing a SQL ROWID Wrapper Interface for JDBC classes which provide the ability to retrieve the delegate instance when the instance in question is in fact a proxy class Availability to be notfied when a PreparedStatement that is associated with a PooledConnection has been closed or the driver determines is invalid Setting and getting of client information for connections: Connection interface's setClientInfo()/getClientInfo() 2. Missing features of JDBC specification(small features) 2-1 ResultSet type TYPE_SCROLL_SENSITIVE rowDeleted() and rowUpdated() of ResultSet interface: currently these methods always return false 2-2 [Description of JDBC spec] 13.6 Retrieving Auto Generated Values It is implementation-defined as to whether Statement.getGeneratedKeys will return generated values after invoking the executeBatch method. [Implementation of PostgreSQL JDBC driver] Statement.getGeneratedKeys() does not return generated values after executing executeBatch(). 2-3 [Description of JDBC spec] 15.1.3.1 Determining ResultSet Holdability The default holdability of ResultSet objects is implementation defined. The DatabaseMetaData method getResultSetHoldability can be called to determine the default holdability of result sets returned by the underlying data source. [Implementation of PostgreSQL JDBC driver] The default holdability of the ResultSet created by createStatement(), prepareStatement(), and prepareCall() of Connection interface is CLOSE_CURSORS_AT_COMMIT. However, DatabaseMetaData.getResultSetHoldability() returns HOLD_CURSORS_OVER_COMMIT. Shouldn't DatabaseMetaData.getResultSetHoldability() return the default holdability HOLD_CURSORS_OVER_COMMIT? 2-4 Distributed transaction support TMRESUME and TMSUSPEND flags are not supported. An empty Xid array is returned unless you pass TMSTARTRSCAN to XAResource.recover(). XAResource.forget() always throws XAException. XA transaction timeout is not supported: XAResource.getTransactionTimeout() always returns 0 and XAResource.setTransactionTimeout() is no-op. The following specification item is not implemented: The transaction manager is not required to use the same XAResource object to commit/rollback a transaction branch as was used to execute the branch. 2-5 Inefficient data retrieval by ResultSet By default, The JDBC driver fetches the entire result data all at once and caches it in the client's memory. setFetchSize() is not effective when you use the scrollable ResultSet. 2-6 Limitations of CallableStatement http://jdbc.postgresql.org/documentation/head/index.html One notable limitation of the current support for a ResultSet created from a refcursor is that even though it is a cursor backed ResultSet, all data will be retrieved and cached on the client. The Statement fetch size parameter described in the section called Getting results based on a cursor is ignored. This limitation is a deficiency of the JDBC driver, not the server, and it is technically possible to remove it, we just haven't found the time. 2-7 positioned updates/deletes are not supported The JDBC driver supports positioned updates and deletes using ResultSet. However, it does not support positioned updates/deletes using SQL commands. i.e. Statement.setCursorName(String name) is no-op. In addition, Connection.setCursorName(String cursor) is no-op. Connection.getCursorName() returns null. Positioned updates/deletes is described in JDBC spec section "15.2.4.4 Positioned Updates and Deletes." 2-8 Query timeout is not supported Statement.setQueryTimeout(int seconds) throws an exception when seconds > 0. 2-9 Checking if columns are writable The following methods of ResultSetMetaData are not implemented. isReadOnly(int column) always returns false isWritable(int column) always returns true isDefinitelyWritable(int column) always returns false 2-10 Statement pooling is not supported The following methods of PooledConnection are no-op. - void addStatementEventListener(StatementEventListener listener) - void removeStatementEventListener(StatementEventListener listener) 2-11 Getting metadata of statement parameters The following methods of ParameterMetaData always return 0. - getPrecision(int param) - getPScale(int param) isNullable(int param) returns ParameterMetaData.parameterNullableUnknown. 3. List of unsupported methods I listed the following methods which call Driver.notImplemented(). <> [JDBC2] public Object getArray(long index, int count, Map map) throws SQLException if map is not empty, or the data type of the array element is neither of the following: BIT, SMALLINT, INTEGER, BIGINT, NUMERIC, REAL, DOUBLE, CHAR, VARCHAR, DATE, TIME, TIMESTAMP [JDBC2] public ResultSet getResultSet(long index, int count, Map map) throws SQLException if map is not empty <> [JDBC4] public java.io.InputStream getBinaryStream(long pos, long length) throws SQLException <> [JDBC2] public Ref getRef(int i) throws SQLException [JDBC2] public Blob getBlob(int i) throws SQLException [JDBC2] public Clob getClob(int i) throws SQLException [JDBC2] public Object getObject(int i, java.util.Map map) throws SQLException if map is not empty [JDBC2] public void registerOutParameter(int parameterIndex, int sqlType, String typeName) throws SQLException [JDBC3] public java.net.URL getURL(int parameterIndex) throws SQLException [JDBC3] public void setURL(String parameterName, java.net.URL val) throws SQLException [JDBC3] public void setURL(int parameterIndex, java.net.URL x) throws SQLException [JDBC3] public void registerOutParameter(String parameterName, int sqlType) throws SQLException [JDBC3] public void registerOutParameter(String parameterName, int sqlType, int scale) throws SQLException [JDBC3] public void registerOutParameter (String parameterName, int sqlType, String typeName) throws SQLException [JDBC3] public void setNull(String parameterName, int sqlType) throws SQLException [JDBC3] public void setBoolean(String parameterName, boolean x) throws SQLException [JDBC3] public void setByte(String parameterName, byte x) throws SQLException [JDBC3] public void setShort(String parameterName, short x) throws SQLException [JDBC3] public void setInt(String parameterName, int x) throws SQLException [JDBC3] public void setLong(String parameterName, long x) throws SQLException [JDBC3] public void setFloat(String parameterName, float x) throws SQLException [JDBC3] public void setDouble(String parameterName, double x) throws SQLException [JDBC3] public void setBigDecimal(String parameterName, BigDecimal x) throws SQLException [JDBC3] public void setString(String parameterName, String x) throws SQLException [JDBC3] public void setBytes(String parameterName, byte x[]) throws SQLException [JDBC3] public void setDate(String parameterName, java.sql.Date x) throws SQLException [JDBC3] public void setTime(String parameterName, java.sql.Time x) throws SQLException [JDBC3] public void setTimestamp(String parameterName, java.sql.Timestamp x) throws SQLException [JDBC3] public void setAsciiStream(String parameterName, java.io.InputStream x, int length) throws SQLException [JDBC3] public void setBinaryStream(String parameterName, java.io.InputStream x, int length) throws SQLException [JDBC3] public void setObject(String parameterName, Object x, int targetSqlType, int scale) throws SQLException [JDBC3] public void setObject(String parameterName, Object x, int targetSqlType) throws SQLException [JDBC3] public void setObject(String parameterName, Object x) throws SQLException [JDBC3] public void setCharacterStream(String parameterName, java.io.Reader reader, int length) throws SQLException [JDBC3] public void setDate(String parameterName, java.sql.Date x, Calendar cal) throws SQLException [JDBC3] public void setTime(String parameterName, java.sql.Time x, Calendar cal) throws SQLException [JDBC3] public void setTimestamp(String parameterName, java.sql.Timestamp x, Calendar cal) throws SQLException [JDBC3] public void setNull (String parameterName, int sqlType, String typeName) throws SQLException [JDBC3] public String getString(String parameterName) throws SQLException [JDBC3] public boolean getBoolean(String parameterName) throws SQLException [JDBC3] public byte getByte(String parameterName) throws SQLException [JDBC3] public short getShort(String parameterName) throws SQLException [JDBC3] public int getInt(String parameterName) throws SQLException [JDBC3] public long getLong(String parameterName) throws SQLException [JDBC3] public float getFloat(String parameterName) throws SQLException [JDBC3] public double getDouble(String parameterName) throws SQLException [JDBC3] public byte[] getBytes(String parameterName) throws SQLException [JDBC3] public java.sql.Date getDate(String parameterName) throws SQLException [JDBC3] public java.sql.Time getTime(String parameterName) throws SQLException [JDBC3] public java.sql.Timestamp getTimestamp(String parameterName) throws SQLException [JDBC3] public Object getObject(String parameterName) throws SQLException [JDBC3] public BigDecimal getBigDecimal(String parameterName) throws SQLException [JDBC3] public Object getObject(String parameterName, java.util.Map map) throws SQLException [JDBC3] public Ref getRef (String parameterName) throws SQLException [JDBC3] public Blob getBlob (String parameterName) throws SQLException [JDBC3] public Clob getClob (String parameterName) throws SQLException [JDBC3] public Array getArray (String parameterName) throws SQLException [JDBC3] public java.sql.Date getDate(String parameterName, Calendar cal) [JDBC3] public java.sql.Time getTime(String parameterName, Calendar cal) [JDBC3] public java.sql.Timestamp getTimestamp(String parameterName, Calendar cal) throws SQLException [JDBC3] public java.net.URL getURL(String parameterName) throws SQLException <> [JDBC2] public long position(String pattern, long start) throws SQLException [JDBC2] public long position(Clob pattern, long start) throws SQLException [JDBC3] public int setString(long pos, String str) throws SQLException [JDBC3] public int setString(long pos, String str, int offset, int len) throws SQLException [JDBC3] public java.io.OutputStream setAsciiStream(long pos) throws SQLException [JDBC3] public java.io.Writer setCharacterStream(long pos) throws SQLException [JDBC4] public Reader getCharacterStream(long pos, long length) throws SQLException <> [JDBC4] public Clob createClob() throws SQLException [JDBC4] public Blob createBlob() throws SQLException [JDBC4] public NClob createNClob() throws SQLException [JDBC4] public Struct createStruct(String typeName, Object[] attributes) throws SQLException [JDBC4] public boolean isValid(int timeout) throws SQLException [JDBC4] public void setClientInfo(String name, String value) throws SQLClientInfoException [JDBC4] public void setClientInfo(Properties properties) throws SQLClientInfoException <> [JDBC3] public ResultSet getSuperTypes(String catalog, String schemaPattern, String typeNamePattern) throws SQLException [JDBC3] public ResultSet getSuperTables(String catalog, String schemaPattern, String tableNamePattern) throws SQLException [JDBC3] public ResultSet getAttributes(String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern) [JDBC4] public RowIdLifetime getRowIdLifetime() throws SQLException [JDBC4] public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException [JDBC4] public ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern) throws SQLException [JDBC4] public ResultSet getFunctionColumns(String catalog, String schemaPattern, String functionNamePattern, String columnNamePattern) throws SQLException <> [JDBC2] public void setRef(int i, Ref x) throws SQLException [JDBC4] public void setRowId(int parameterIndex, RowId x) throws SQLException [JDBC4] public void setNString(int parameterIndex, String value) throws SQLException [JDBC4] public void setNCharacterStream(int parameterIndex, Reader value, long length) throws SQLException [JDBC4] public void setNCharacterStream(int parameterIndex, Reader value) throws SQLException [JDBC4] public void setCharacterStream(int parameterIndex, Reader value, long length) throws SQLException [JDBC4] public void setCharacterStream(int parameterIndex, Reader value) throws SQLException [JDBC4] public void setBinaryStream(int parameterIndex, InputStream value, long length) throws SQLException [JDBC4] public void setBinaryStream(int parameterIndex, InputStream value) throws SQLException [JDBC4] public void setAsciiStream(int parameterIndex, InputStream value, long length) throws SQLException [JDBC4] public void setAsciiStream(int parameterIndex, InputStream value) throws SQLException [JDBC4] public void setNClob(int parameterIndex, NClob value) throws SQLException [JDBC4] public void setClob(int parameterIndex, Reader reader, long length) throws SQLException [JDBC4] public void setClob(int parameterIndex, Reader reader) throws SQLException [JDBC4] public void setBlob(int parameterIndex, InputStream inputStream, long length) throws SQLException [JDBC4] public void setBlob(int parameterIndex, InputStream inputStream) throws SQLException [JDBC4] public void setNClob(int parameterIndex, Reader reader, long length) throws SQLException [JDBC4] public void setNClob(int parameterIndex, Reader reader) throws SQLException <> [JDBC2] public Object getObject(int i, Map map) throws SQLException [JDBC2] public Object getObject(String s, Map map) throws SQLException if map is not empty [JDBC2] public Ref getRef(String columnName) throws SQLException [JDBC2] public Ref getRef(int i) throws SQLException [JDBC3] public java.net.URL getURL(int columnIndex) throws SQLException [JDBC3] public java.net.URL getURL(String columnName) throws SQLException [JDBC3] public void updateRef(int columnIndex, java.sql.Ref x) throws SQLException [JDBC3] public void updateRef(String columnName, java.sql.Ref x) throws SQLException [JDBC3] public void updateBlob(int columnIndex, java.sql.Blob x) throws SQLException [JDBC3] public void updateBlob(String columnName, java.sql.Blob x) throws SQLException [JDBC3] public void updateClob(int columnIndex, java.sql.Clob x) throws SQLException [JDBC3] public void updateClob(String columnName, java.sql.Clob x) throws SQLException [JDBC4] public RowId getRowId(int columnIndex) throws SQLException [JDBC4] public RowId getRowId(String columnName) throws SQLException [JDBC4] public void updateRowId(int columnIndex, RowId x) throws SQLException [JDBC4] public void updateRowId(String columnName, RowId x) throws SQLException [JDBC4] public int getHoldability() throws SQLException [JDBC4] public void updateNString(int columnIndex, String nString) throws SQLException [JDBC4] public void updateNString(String columnName, String nString) throws SQLException [JDBC4] public void updateNClob(int columnIndex, NClob nClob) throws SQLException [JDBC4] public void updateNClob(String columnName, NClob nClob) throws SQLException [JDBC4] public void updateNClob(int columnIndex, Reader reader) throws SQLException [JDBC4] public void updateNClob(String columnName, Reader reader) throws SQLException [JDBC4] public void updateNClob(int columnIndex, Reader reader, long length) throws SQLException [JDBC4] public void updateNClob(String columnName, Reader reader, long length) throws SQLException [JDBC4] public NClob getNClob(int columnIndex) throws SQLException [JDBC4] public NClob getNClob(String columnName) throws SQLException [JDBC4] public void updateBlob(int columnIndex, InputStream inputStream, long length) throws SQLException [JDBC4] public void updateBlob(String columnName, InputStream inputStream, long length) throws SQLException [JDBC4] public void updateBlob(int columnIndex, InputStream inputStream) throws SQLException [JDBC4] public void updateBlob(String columnName, InputStream inputStream) throws SQLException [JDBC4] public void updateClob(int columnIndex, Reader reader, long length) throws SQLException [JDBC4] public void updateClob(String columnName, Reader reader, long length) throws SQLException [JDBC4] public void updateClob(int columnIndex, Reader reader) throws SQLException [JDBC4] public void updateClob(String columnName, Reader reader) throws SQLException [JDBC4] public String getNString(int columnIndex) throws SQLException [JDBC4] public String getNString(String columnName) throws SQLException [JDBC4] public Reader getNCharacterStream(int columnIndex) throws SQLException [JDBC4] public Reader getNCharacterStream(String columnName) throws SQLException [JDBC4] public void updateNCharacterStream(int columnIndex, Reader x, int length) throws SQLException [JDBC4] public void updateNCharacterStream(String columnName, Reader x, int length) throws SQLException [JDBC4] public void updateNCharacterStream(int columnIndex, Reader x) throws SQLException [JDBC4] public void updateNCharacterStream(String columnName, Reader x) throws SQLException [JDBC4] public void updateNCharacterStream(String columnName, Reader x, long length) throws SQLException [JDBC4] public void updateCharacterStream(int columnIndex, Reader reader, long length) throws SQLException [JDBC4] public void updateCharacterStream(int columnIndex, Reader reader) throws SQLException [JDBC4] public void updateCharacterStream(String columnName, Reader reader) throws SQLException [JDBC4] public void updateBinaryStream(int columnIndex, InputStream inputStream, long length) throws SQLException [JDBC4] public void updateBinaryStream(String columnName, InputStream inputStream, long length) throws SQLException [JDBC4] public void updateBinaryStream(int columnIndex, InputStream inputStream) throws SQLException [JDBC4] public void updateBinaryStream(String columnName, InputStream inputStream) throws SQLException [JDBC4] public void updateAsciiStream(int columnIndex, InputStream inputStream, long length) throws SQLException [JDBC4] public void updateAsciiStream(String columnName, InputStream inputStream, long length) throws SQLException [JDBC4] public void updateAsciiStream(int columnIndex, InputStream inputStream) throws SQLException <> [JDBC2] public void setQueryTimeout(int seconds) throws SQLException if seconds is greater than 0 <> The following methods are not implemented in all classes that implement Wrapper interface. [JDBC4] public boolean isWrapperFor(Class iface) throws SQLException [JDBC4] public T unwrap(Class iface) throws SQLException 4. Regression test Some tests are not run when you simply follow the test execution procedure on the JDBC project web site. A few steps need to be added. 4-1 To run the distributed transaction (XA) tests: Set max_prepared_transactions parameter in postgresql.conf. The value needs to be equal to or greater than max_connections. 4-2 To run Unicode tests: Create test database with Unicode encoding as follows: createdb -U test -E UTF8 -T template0 test 4-3 It is kind to instruct the users that they should set def_pgport parameter in build.local.properties. I didn't notice this and encountered an error when I ran the test for the first time. 4-4 Ability to run regression tests against the driver located in an arbitrary place The current regression test builds the driver and uses it. It is desirable to run the regression test against an installed driver like "make installcheck" of PostgreSQL. With that feature, It would be possible to make sure that the installation media contains a correct driver and we performed packaging procedure with no problem.