pgjdbc / pgjdbc

Postgresql JDBC Driver

PostgreSQL JDBC Driver

PostgreSQL JDBC Driver (PgJDBC for short) allows Java programs to connect to a PostgreSQL database using standard, database independent Java code. Is an open source JDBC driver written in Pure Java (Type 4), and communicates in the PostgreSQL native network protocol.

Status

Supported PostgreSQL and Java versions

The current version of the driver should be compatible with PostgreSQL 8.4 and higher using the version 3.0 of the protocol and Java 8 (JDBC 4.2) or above. Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using.

PgJDBC regression tests are run against all PostgreSQL versions since 9.1, including "build PostgreSQL from git master" version. There are other derived forks of PostgreSQL but they have not been certified to run with PgJDBC. If you find a bug or regression on supported versions, please file an Issue.

Get the Driver

Most people do not need to compile PgJDBC. You can download the precompiled driver (jar) from the PostgreSQL JDBC site or using your chosen dependency management tool:

Maven Central

You can search on The Central Repository with GroupId and ArtifactId org.postgresql:postgresql.

<!-- Add the following dependency to your pom.xml, -->
<!-- replacing LATEST with specific version as required -->

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

Development snapshots

Snapshot builds (builds from master branch) are also deployed to OSS Sonatype Snapshot Repository, so you can test current development version (test some bugfix) by enabling the repository and using the latest SNAPSHOT version.

There are also available (snapshot) binary RPMs in Fedora's Copr repository.

---

Documentation

For more information you can read the PgJDBC driver documentation or for general JDBC documentation please refer to The Java™ Tutorials.

Driver and DataSource class

Implements	Class
java.sql.Driver	org.postgresql.Driver
javax.sql.DataSource	org.postgresql.ds.PGSimpleDataSource
javax.sql.ConnectionPoolDataSource	org.postgresql.ds.PGConnectionPoolDataSource
javax.sql.XADataSource	org.postgresql.xa.PGXADataSource

Building the Connection URL

The driver recognises JDBC URLs of the form:

jdbc:postgresql:database
jdbc:postgresql:
jdbc:postgresql://host/database
jdbc:postgresql://host/
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/
jdbc:postgresql://?service=myservice

The general format for a JDBC URL for connecting to a PostgreSQL server is as follows, with items in square brackets ([ ]) being optional:

jdbc:postgresql:[//host[:port]/][database][?property1=value1[&property2=value2]...]

where:

• jdbc:postgresql: (Required) is known as the sub-protocol and is constant.
• host (Optional) is the server address to connect. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer. To specify an IPv6 address your must enclose the host parameter with square brackets (jdbc:postgresql://[::1]:5740/accounting). Defaults to localhost.
• port (Optional) is the port number listening on the host. Defaults to 5432.
• database (Optional) is the database name. Defaults to the same name as the user name used in the connection.
• propertyX (Optional) is one or more option connection properties. For more information see Connection properties.

Logging

PgJDBC uses java.util.logging for logging. To configure log levels and control log output destination (e.g. file or console), configure your java.util.logging properties accordingly for the org.postgresql logger. Note that the most detailed log levels, "FINEST", may include sensitive information such as connection details, query SQL, or command parameters.

Connection Properties

In addition to the standard connection parameters the driver supports a number of additional properties which can be used to specify additional driver behaviour specific to PostgreSQL™. These properties may be specified in either the connection URL or an additional Properties object parameter to DriverManager.getConnection.

Property	Type	Default	Description
user	String	null	The database user on whose behalf the connection is being made.
password	String	null	The database user's password.
options	String	null	Specify 'options' connection initialization parameter.
service	String	null	Specify 'service' name described in pg_service.conf file. References: The Connection Service File and The Password File. 'service' file can provide all properties including 'hostname=', 'port=' and 'dbname='.
ssl	Boolean	false	Control use of SSL (true value causes SSL to be required)
sslfactory	String	org.postgresql.ssl.LibPQFactory	Provide a SSLSocketFactory class when using SSL.
sslfactoryarg (deprecated)	String	null	Argument forwarded to constructor of SSLSocketFactory class.
sslmode	String	prefer	Controls the preference for opening using an SSL encrypted connection.
sslcert	String	null	The location of the client's SSL certificate
sslkey	String	null	The location of the client's PKCS#8 or PKCS#12 SSL key, for PKCS the extension must be .p12 or .pfx and the alias must be user
sslrootcert	String	null	The location of the root certificate for authenticating the server.
sslhostnameverifier	String	null	The name of a class (for use in Class.forName(String)) that implements javax.net.ssl.HostnameVerifier and can verify the server hostname.
sslpasswordcallback	String	null	The name of a class (for use in Class.forName(String)) that implements javax.security.auth.callback.CallbackHandler and can handle PasswordCallback for the ssl password.
sslpassword	String	null	The password for the client's ssl key (ignored if sslpasswordcallback is set)
sendBufferSize	Integer	-1	Socket write buffer size
receiveBufferSize	Integer	-1	Socket read buffer size
logServerErrorDetail	Boolean	true	Allows server error detail (such as sql statements and values) to be logged and passed on in exceptions. Setting to false will mask these errors so they won't be exposed to users, or logs.
allowEncodingChanges	Boolean	false	Allow for changes in client_encoding
logUnclosedConnections	Boolean	false	When connections that are not explicitly closed are garbage collected, log the stacktrace from the opening of the connection to trace the leak source
binaryTransfer	Boolean	true	Use binary format for sending and receiving data if possible. Setting this to false disables any binary transfer
binaryTransferEnable	String	""	Comma separated list of types to enable binary transfer. Either OID numbers or names
binaryTransferDisable	String	""	Comma separated list of types to disable binary transfer. Either OID numbers or names. Overrides values in the driver default set and values set with binaryTransferEnable.
prepareThreshold	Integer	5	Statement prepare threshold. A value of -1 stands for forceBinary
preparedStatementCacheQueries	Integer	256	Specifies the maximum number of entries in per-connection cache of prepared statements. A value of 0 disables the cache.
preparedStatementCacheSizeMiB	Integer	5	Specifies the maximum size (in megabytes) of a per-connection prepared statement cache. A value of 0 disables the cache.
defaultRowFetchSize	Integer	0	Positive number of rows that should be fetched from the database when more rows are needed for ResultSet by each fetch iteration
loginTimeout	Integer	0	Specify how long in seconds max(2147484) to wait for establishment of a database connection.
connectTimeout	Integer	10	The timeout value in seconds max(2147484) used for socket connect operations.
socketTimeout	Integer	0	The timeout value in seconds max(2147484) used for socket read operations.
cancelSignalTimeout	Integer	10	The timeout that is used for sending cancel command.
sslResponseTimeout	Integer	5000	Socket timeout in milliseconds waiting for a response from a request for SSL upgrade from the server.
tcpKeepAlive	Boolean	false	Enable or disable TCP keep-alive.
tcpNoDelay	Boolean	true	Enable or disable TCP no delay.
ApplicationName	String	PostgreSQL JDBC Driver	The application name (require server version >= 9.0). If assumeMinServerVersion is set to >= 9.0 this will be sent in the startup packets, otherwise after the connection is made
readOnly	Boolean	false	Puts this connection in read-only mode
readOnlyMode	String	transaction	Specifies the behavior when a connection is set to be read only, possible values: ignore, transaction, always
disableColumnSanitiser	Boolean	false	Enable optimization that disables column name sanitiser
assumeMinServerVersion	String	null	Assume the server is at least that version
currentSchema	String	null	Specify the schema (or several schema separated by commas) to be set in the search-path
targetServerType	String	any	Specifies what kind of server to connect, possible values: any, master, slave (deprecated), secondary, preferSlave (deprecated), preferSecondary, preferPrimary
hostRecheckSeconds	Integer	10	Specifies period (seconds) after which the host status is checked again in case it has changed
loadBalanceHosts	Boolean	false	If disabled hosts are connected in the given order. If enabled hosts are chosen randomly from the set of suitable candidates
socketFactory	String	null	Specify a socket factory for socket creation
socketFactoryArg (deprecated)	String	null	Argument forwarded to constructor of SocketFactory class.
autosave	String	never	Specifies what the driver should do if a query fails, possible values: always, never, conservative
cleanupSavepoints	Boolean	false	In Autosave mode the driver sets a SAVEPOINT for every query. It is possible to exhaust the server shared buffers. Setting this to true will release each SAVEPOINT at the cost of an additional round trip.
preferQueryMode	String	extended	Specifies which mode is used to execute queries to database, possible values: extended, extendedForPrepared, extendedCacheEverything, simple
reWriteBatchedInserts	Boolean	false	Enable optimization to rewrite and collapse compatible INSERT statements that are batched.
escapeSyntaxCallMode	String	select	Specifies how JDBC escape call syntax is transformed into underlying SQL (CALL/SELECT), for invoking procedures or functions (requires server version >= 11), possible values: select, callIfNoReturn, call
maxResultBuffer	String	null	Specifies size of result buffer in bytes, which can't be exceeded during reading result set. Can be specified as particular size (i.e. "100", "200M" "2G") or as percent of max heap memory (i.e. "10p", "20pct", "50percent")
gssLib	String	auto	Permissible values are auto (default, see below), sspi (force SSPI) or gssapi (force GSSAPI-JSSE).
gssResponseTimeout	Integer	5000	Socket timeout in milliseconds waiting for a response from a request for GSS encrypted connection from the server.
gssEncMode	String	allow	Controls the preference for using GSSAPI encryption for the connection, values are disable, allow, prefer, and require
useSpnego	String	false	Use SPNEGO in SSPI authentication requests
adaptiveFetch	Boolean	false	Specifies if number of rows fetched in ResultSet by each fetch iteration should be dynamic. Number of rows will be calculated by dividing maxResultBuffer size into max row size observed so far. Requires declaring maxResultBuffer and defaultRowFetchSize for first iteration.
adaptiveFetchMinimum	Integer	0	Specifies minimum number of rows, which can be calculated by adaptiveFetch. Number of rows used by adaptiveFetch cannot go below this value.
adaptiveFetchMaximum	Integer	-1	Specifies maximum number of rows, which can be calculated by adaptiveFetch. Number of rows used by adaptiveFetch cannot go above this value. Any negative number set as adaptiveFetchMaximum is used by adaptiveFetch as infinity number of rows.
localSocketAddress	String	null	Hostname or IP address given to explicitly configure the interface that the driver will bind the client side of the TCP/IP connection to when connecting.
quoteReturningIdentifiers	Boolean	true	By default we double quote returning identifiers. Some ORM's already quote them. Switch allows them to turn this off
authenticationPluginClassName	String	null	Fully qualified class name of the class implementing the AuthenticationPlugin interface. If this is null, the password value in the connection properties will be used.
unknownLength	Integer	Integer.MAX_LENGTH	Specifies the length to return for types of unknown length
stringtype	String	null	Specify the type to use when binding PreparedStatement parameters set via setString()

System Properties

Property	Type	Default	Description
pgjdbc.config.cleanup.thread.ttl	long	30000	The driver has an internal cleanup thread which monitors and cleans up unclosed connections. This property sets the duration (in milliseconds) the cleanup thread will keep running if there is nothing to clean up.

Contributing

For information on how to contribute to the project see the Contributing Guidelines

---

Sponsors

• PostgreSQL International

pgjdbc's Issues

Rewrite TimestampTest.java to reduce repetition, handle rounding errors in float timestamps

(This is really a not-to-self about a task I intend to tackle, but don't have time for immediately):

TimestampTest produces false-negatives due to older servers' floating point timestamps creating rounding errors. This may hide legitimate failures, and makes it impossible to set builds to fail when tests fail on older servers.

I need to fix that. In the process I might as well rewrite TimestampTest to reduce the huge amount of duplication. Adding some data structures to hold the timestamps and looping over them will help a lot when it comes to adding new corner cases and tests later. It'll also make it practical to do rounding-safe comparisions.

java.sql.DatabaseMetaData.getImportedKeys()/getMaxIndexKeys() fails against Amazon Redshift

When using Mondrian schema workbench, it is unable to list databases in the schema, the error is: org.postgresql.util.PSQLException: "Unable to determine a value for MaxIndexKeys due to missing system catalog data."

It appears to be incompatibility of SQL used to query metadata and the version which Redshift presents itself as:
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java#L46-59

The query it tries to run reporting as an 8.0 PostgreSQL:
SELECT * FROM pg_catalog.pg_settings WHERE name='max_index_keys'

Query that actually works (Min version >= 7.3 query):
SELECT t1.typlen/t2.typlen from pg_catalog.pg_namespace n, pg_catalog.pg_type t1, pg_catalog.pg_type t2 WHERE t1.typnamespace=n.oid AND n.nspname='pg_catalog' AND t1.typelem=t2.oid AND t1.typname='oidvector'

Is there a way to determine a Redshift version within the driver? Set something in JDBC configuration for Redshift?

Error when trying to close PGCopyInputStream which was read till EOF

org.postgresql.util.PSQLException: Tried to cancel an inactive copy operation
    at org.postgresql.core.v3.QueryExecutorImpl.cancelCopy(QueryExecutorImpl.java:761)
    at org.postgresql.core.v3.CopyOperationImpl.cancelCopy(CopyOperationImpl.java:30)
    at org.postgresql.copy.PGCopyInputStream.close(PGCopyInputStream.java:120)
    at org.postgresql.test.jdbc4.PGCopyInputStreamTest.testStreamCanBeClosedAfterReadUp(PGCopyInputStreamTest.java:69)
    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:601)
    at junit.framework.TestCase.runTest(TestCase.java:176)
    at junit.framework.TestCase.runBare(TestCase.java:141)
    at junit.framework.TestResult$1.protect(TestResult.java:122)
    at junit.framework.TestResult.runProtected(TestResult.java:142)
    at junit.framework.TestResult.run(TestResult.java:125)
    at junit.framework.TestCase.run(TestCase.java:129)
    at junit.framework.TestSuite.runTest(TestSuite.java:255)
    at junit.framework.TestSuite.run(TestSuite.java:250)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:84)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Add method to define default Schema name at the connection itself

Send extra_float_digits at protocol level

Right now we SET extra_float_digits at the SQL level:

        if (dbVersion.compareTo("9.0") >= 0) {
            SetupQueryRunner.run(protoConnection, "SET extra_float_digits = 3", false);
        }

(in org/postgresql/core/v3/ConnectionFactoryImpl.java)

That spams the user's query log and leaves traces in pg_stat_activity. We should be using protocol-level var setting instead, or if possible supplying it in the connection setup packet.

Assigning to self.

Fix disabling prepared statements

A lot of people use pgbouncer to scale postgresql to more connections but that requires disabling prepared statements.

See
http://pgbouncer.projects.pgfoundry.org/doc/faq.html#_disabling_prepared_statements_in_jdbc

The latest driver (9.2-1002) still does not work. After upgrading server to 9.2 and upgrading the jdbc driver to latest we can't get the pgbouncer working again. We're not alone. A lot of people have the same problem, fork their driver, etc.

Can it be fixed? Or does at least anybody know how to patch the latest driver, please?

Use protocol-level Sync message to force round-trip when flushing notifications

Currently the docs advise users to send a SELECT 1 to avoid blocking when getting notifications.

Instead we should automatically detect if we might block (as is the case with Java's primitive SSL sockets when we're in SSL mode) and send a protocol level Sync message to force a round-trip without spamming the logs.

Meanwhile, the docs should advise users to send an empty query "" not a "SELECT 1;".

(This is a TODO item for me).

Enable batching when returning generated keys

According to my notes here:

http://stackoverflow.com/questions/12639118/jdbc-batch-performance/12648498#12648498

PgJDBC doesn't send statement batches server side for execution if it has to fetch generated keys. This might be nice to support, so I should bug -hackers to see if there's any reasonable way to do it with the current fe/be protocol and if not, get it TODOed for the next protocol revision.

Test against the Java SE 6 TCK

It'd be good to start testing PgJDBC against the Java SE 6 TCK, if we can. It's a bit of a pain in that the TCK is part of the greater Java SE 6 TCK, which has annoying and painful licensing requirements.

It might be worth bugging Lance, the JDBC 4.1 spec lead at Oracle, to see whether there's any chance of a stand-alone TCK, or of friendlier licensing for the SE6 TCK for the JDBC component only.

TODO.

AbstractJdbc2Statement.setArray does not work with java.sql.Array implementation with general jdbc type name (integer v.s. int4)

I think there is a bug in version 9.1-902 which won't support java.sql.Array implementation with general base type name instead of PostgreSQL data type ( integer v.s. int4)

looking at org.postgresql.jdbc2.TypeInfoCache.

if user creates java.sql.Array by invoking org.postgresql.AbstractJdbc4Connection#createArrayOf(String, Object[]), underlining org.postgresql.jdbc2.TypeInfoCache.getPGArrayType will use alias to convert some general jdbc type names to PG 's native 'real' type names:

/**
     * PG maps several alias to real type names.  When we do queries
     * against pg_catalog, we must use the real type, not an alias, so
     * use this mapping.
     */
    private final static HashMap typeAliases;
    static {
        typeAliases = new HashMap();
        typeAliases.put("smallint", "int2");
        typeAliases.put("integer", "int4");
        typeAliases.put("int", "int4");
        typeAliases.put("bigint", "int8");
        typeAliases.put("float", "float8");
        typeAliases.put("boolean", "bool");
        typeAliases.put("decimal", "numeric");
    }

however, if user builds java.sql.Array implementation by themselves and use general jdbc type name i.e. "integer". and calling setArray method on org.postgresql.jdbc2.AbstractJdbc2Statement. PG jdbc driver will not lookup type alias and directly use the baseTypeName return from underline implementation of java.sql.Array. Should don't pgjdbc look up type alias here before appending "_" on the typename?

public void setArray(int i, java.sql.Array x) throws SQLException
    {
        checkClosed();

        if (null == x)
        {
            setNull(i, Types.ARRAY);
            return;
        }

        // This only works for Array implementations that return a valid array
        // literal from Array.toString(), such as the implementation we return
        // from ResultSet.getArray(). Eventually we need a proper implementation
        // here that works for any Array implementation.

        // Use a typename that is "_" plus the base type; this matches how the
        // backend looks for array types.
        String typename = "_" + x.getBaseTypeName();
        int oid = connection.getTypeInfo().getPGType(typename);
        if (oid == Oid.UNSPECIFIED)
            throw new PSQLException(GT.tr("Unknown type {0}.", typename), PSQLState.INVALID_PARAMETER_TYPE);

        setString(i, x.toString(), oid);
    }

Should complain about invalid synax in DSN (multiple colons)

A DSN of the form jdbc:postgresql://host:5432:dbname will silently ignore the :dbname part and connect to the default postgres database instead. There should be an exception to notify the user, otherwise they will wonder where their connection went. (The issue would be much less annoying if the connection would fail, but postgres is a valid database name, so it will take a while to notice what went wrong.)

Driver.java.in parseURL() looks like where this should happen.

Create shell script to test with common configurations

(TODO I want to remember and can't immediately start):

I have some shell aliases that I've been using to test with different JDKs, but they aren't great.

I need to write a shell script that tests with select points from the (jdk,pg) version matrix and records the results, with each build set to fail if any test fails. This script will need to be able to start and stop servers, know and set JAVA_HOME and PATH, etc. It should set a non-default port for Pg when testing, so we don't have to stop any system Pg.

Another script to check a given Pg release out from git, build it, install it, initdb it, and create the test db and user will be a good companion and will make setting up the test environment much easier. The tool should use a non-default port so servers don't clash with any system Pg.

This may require an enhancement to the test cases to allow the server port to connect to to be set at the ant command line.

Convert project to maven

The project build is based on Ant and has a few disadvantages:

1. There is no default way to import it to IDE, manual import and setteings are required
2. compile conflicts
3. Hard to test
4. manual library settings
5. Non-obvious tricks with java templates (Driver.java.in)
6. All sources mixed, tests in the same source scope
7. maven plugin via ant integrations

Convert to maven will:

1. Allow IDE auto import without compile conflicts
2. Save the whole history of source files (thanks to git), but this should be a huge patchset (almost all the files would be moved somewhere).
3. make a default way to build projects
4. Increase army of contributors :)
5. It is easy to check, if both ant and maven builds will make the equal jar files (different only in pom descriptors), that means, that it's probably correct.

I'm sure, you already know all it without me. But somewhy, the project is still ant-based.

In general I see the main issue about support old jdk versions and different jdbc interfaces. It can be resolved by splitting the project to separate modules like:

• pg-core the whole abstract model, jdbc-independent, no Driver.java
• pg-jdbc3 non-abstract jdbc3 (not included by default in general pom), Driver.java for jdbc 3
• pg-jdbc3g non-abstract, Driver.java for 3g (not included)
• pg-jdbc4 (included by default), Driver.java for 4

Please, tell me if somebody is already doing this. If you don't mind, I'll experiment with the idea and prepare the pull request. Not sure, that current project is fine for that (the directory structure should be absolutely other), maybe a new in pgjdbc/ group?

Support primitive arrays like String[], int[] in setObject(...)

See http://archives.postgresql.org/message-id/[email protected] and thread

Seems to be something other vendor drivers do, though non-standard. Must investigate.

Support DatabaseMetadata.getFunctions

See http://archives.postgresql.org/message-id/CAFEjsq53zVr_8h+=[e-mail], http://archives.postgresql.org/message-id/CADK3HH+jJO-RrHFcJfiqbDtiZ=mqSXvtXzV4r=[e-mail]

Add OSGi metadata

Current jar do not contain metadata required by OSGi containers so it's not possible to use the driver under OSGi without manual jar repackaging. Please, add metadata.

Specifying large number of parameters brokes things

For example, to delete many records I build long query like this:
delete .... where id in (?,?, ...... ?)

and I set parameters for query, in such way
stmt.setObject(i, obj).

In case where there are a LOT of parameters (seems that when more than 32k or 64k),
I got
a) error from driver - IO error or sometimes error about unexpected size of int
b) I can't get connection by jdbc url anymore after error - keep getting that "This connetion has been closed".

However, I'm using JBoss with connection pools, so "this connection" shouldn't be closed.

Server version support policy - 7.2?

The website says PgJDBC supports "Postgresql 7.2 or newer". The last release of 7.2 was 7.2.8 in 2005-05-09.

The most recent supported version of PostgreSQL by the team is 8.3, though I see 8.2 come up often enough to think it's still out there. But 7.2? Is it reasonable to ask people running servers that incredibly ancient to just use an older PgJDBC?

I'd like to document the supported server version in the README, and in the process verify that the proposed REL9_2_STABLE will actually work on the claimed server versions. I can't help but think that the REL9_2_STABLE branch is an opportunity to cut the supported servers from 7 years old to say the oldest version supported by the main project (8.3) to reduce maintenance burden down the track?

@davecramer, you're overworked. I'm limited in how much time I can commit too. Is it reasonable to test and support 7+-year-old servers?

(I initially wrote 12 years. Apparently my maths isn't, tonight. Sorry.)

Add support for setObject(<arg>, null)

This has been implemented by drivers and/or engines of almost all other databases I am aware of, and a description and discussion can be found at:

https://issues.apache.org/jira/browse/DERBY-1938

From the practical perspective, JPA does not provide us with any choice because it does not support type casting.

JPQL like the following from http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples should just work:

SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName

On JDBC level:

SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))

Support for Arrays of Composite Types

I discovered that although user defined types registration is implemented in version 9.2-1002 the JDBC driver does not take advantage of it

any attempt to map user defined types ends with exception

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) is not yet implemented.

I have used both PGConnection.addDataType and Jdbc4Array,gerArray(Map<String, Class <?>>)
both techniques ended with the exception above

I have located the source code part that generates the Exception and observed that the exception is always generated when the base type is not one of the default types

it seems support for user defined types is incomplete
are there any plans for finishing this implementation in the future?
are there any workarounds I am not aware of? or a replacement technique?

Deploy 9.2-1002 to Maven Central

The latest version found in Maven Central is 'postgresql:postgresql:9.1-901.jdbc4'. Someone else already did a 3rd-party deploy of 'org.jumpmind.symmetric.jdbc:postgresql:9.2-1002-jdbc4', but it would be nice to keep the same group and artifact ID in Maven builds.

Resultsets of arrays of composite types unsupported: Method org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long,int,Map) is not yet implemented

See:

http://sqlfiddle.com/#!12/8ccb7/1

CREATE TYPE ct AS (a text, b integer);
SELECT ARRAY[ ('a',1)::ct, ('b',2)::ct ];

splat: Method org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long,int,Map) is not yet implemented

PGCopyInputStream incorrectly handles EOF when reading to buffer

These methods returns zero on end of stream

int PGCopyInputStream.read(byte[] buf)
int PGCopyInputStream.read(byte[] buf, int off, int size)

This violates contract of read which should return -1 on EOF.

Prepared statement pool and Daylight Saving Time

When you try to retrieve the same Timestamp multiple times on some iteration (4th ... 17th) it may return the time that is one hour off.

data type: timestamp without time zone

The problem exists only in PostgreSql when prepared statement pool is used.

e.g. I store 2014-03-09 06:21:28 but JDBC may returns 2014-03-09 07:21:28
key is "may return". See the tests.

in Eastern Time Zone
http://www.timetemperature.com/canada/daylight_saving_time_canada.shtml

(+1 hour)
Dates: DST Begins at 2 a.m.
Hours that will produce this problem 3AM, 4AM, 5AM and 6AM

(-1 hour)
DST Ends at 2 a.m.
Hours that will produce this problem 1AM, 2AM, 3AM, and 4AM

Statement pool C3P0 or DBCP2 was used during tests.
MySQL, Oracle and HSQLDB divers do not show similar problem.

Envelopment:
Oracle Java 1.7.
postgresql-9.2-1002-jdbc4.jar or postgresql-9.3-1101-jdbc41.jar
PostgreSQL 9.1.5 (Ubuntu 12.04.4 or Windows 7 )
or PostgreSQL 9.3.3 on Windows 7

Tests provided will fail in Eastern Time Zone (e.g. NY/USA)

Tests inside pgjdbc code base. (need to add C3P0 or DBCP2 jar to classpath)
https://github.com/skarzhevskyy/pgjdbc/tree/d1b89b42fbe3d214155d3eb35a00864438ddfcf1/org/postgresql/test/other

Standalone test:
https://github.com/skarzhevskyy/pgjdbc/blob/d1b89b42fbe3d214155d3eb35a00864438ddfcf1/src/postgresql-integration/src/test/java/org/postgresql/test/other/DaylightSavingJDBC.java

with maven project since dependencies required to create statement pool
https://github.com/skarzhevskyy/pgjdbc/tree/d1b89b42fbe3d214155d3eb35a00864438ddfcf1/src/postgresql-integration

Incorrect Returns for Floats Cast to Strings

We're experiencing an issue where casts to string types in a view don't seem to have parity behavior between PSQL and the JDBC driver. This affects at least the latest driver version 9.3-1100 with PSQL 9.3.1 backed by postgres server 9.3.1-1.pgdg12.4+1

Test case (execute with PSQL):

create table t1 (c1 float8);
insert into t1 (0.59);
create view v1 as select c1::text from t1;

Now run the following query in PSQL and with the JDBC driver:

select c1 from v1;

With the JDBC driver the result of the select is 0.589999999999999969.

In PSQL the result of the select is 0.59.

Going further, if I run the following through the JDBC driver:

create table t2 as select * from v1;

Now read from JDBC driver:

select c1 from t2; -> 0.589999999999999969

Now read from PSQL:

select c1 from t2; -> 0.589999999999999969

Running create in PSQL though.....

create table t3 as select * from v1;

Read from JDBC driver:

select c1 from t3; -> 0.59

Read from PSQL:

select c1 from t3; -> 0.59

Choosing Ambiguous Columns

The JDBC spec says when you have duplicate columns names, the first one should be returned but this behavior isn't useful to us. What we really need is the ability to resolve those ambiguous cases in the client code.

I propose adding an additional string to the columnNameIndexMap that would be in the format of tablename.column similar to how you would reference the columns in SQL.

I've added a test case for this use case that shows that it works and doesn't affect any existing test cases.

CopyManager throws wrong exception when setting DateStyle

When trying to set DateStyle before a COPY operation through CopyManager, the SQLExceptions that it throws are wrong.
You can try it with the following Maven-based project:
https://www.dropbox.com/s/663132bj1kolqv9/jdbc-bug-poc.zip
Using PG JDBC version 9.3-1101-jdbc41
Using PGSql server version 9.2.4

WrongQueryMain: executes a wrong query for a COPY.
WrongDataMain: executes a correct query but with wrong data.
The table to create is inside src/etc/sql/create.sql

The wrong exceptions are, in particular:

1. if the syntax of the query is wrong, it fails this way:

Exception in thread "main" org.postgresql.util.PSQLException: Database connection failed when starting copy
    at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:730)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:52)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:139)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:124)
    at sample.WrongQueryMain.main(WrongQueryMain.java:29)
Caused by: java.io.IOException: Unexpected packet type during copy: 83
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1060)
    at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:728)
    ... 4 more

If the data is wrong it fails this way:

Exception in thread "main" org.postgresql.util.PSQLException: Database connection failed when canceling copy operation
    at org.postgresql.core.v3.QueryExecutorImpl.cancelCopy(QueryExecutorImpl.java:798)
    at org.postgresql.core.v3.CopyOperationImpl.cancelCopy(CopyOperationImpl.java:30)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:148)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:124)
    at sample.WrongDataMain.main(WrongDataMain.java:30)
Caused by: java.io.IOException: Unexpected packet type during copy: 0
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1060)
    at org.postgresql.core.v3.QueryExecutorImpl.cancelCopy(QueryExecutorImpl.java:780)
    ... 4 more

Build fails under JDK 1.5

While reviewing kdubb's patch to DatabaseMetaData.getTypeInfo(), I thought I'd best test the driver against JDK 5. It doesn't build, though it built quite happily with JDK 7.

[javac] /home/craig/projects/pgjdbc/org/postgresql/Driver.java:393: cannot find symbol
[javac] symbol  : constructor Jdbc3gConnection(java.net.InetSocketAddress[],java.lang.String,java.lang.String,java.util.Properties,java.lang.String)
[javac] location: class org.postgresql.jdbc3g.Jdbc3gConnection
[javac]         return new org.postgresql.jdbc3g.Jdbc3gConnection(address(props),
[javac]                ^
[javac] /home/craig/projects/pgjdbc/org/postgresql/jdbc3/Jdbc3Connection.java:22: cannot find symbol
[javac] symbol  : constructor AbstractJdbc3Connection(java.lang.String,int,java.lang.String,java.lang.String,java.util.Properties,java.lang.String)
[javac] location: class org.postgresql.jdbc3.AbstractJdbc3Connection
[javac]         super(host, port, user, database, info, url);
[javac]         ^
[javac] /home/craig/projects/pgjdbc/org/postgresql/jdbc3g/Jdbc3gConnection.java:22: cannot find symbol
[javac] symbol  : constructor AbstractJdbc3gConnection(java.lang.String,int,java.lang.String,java.lang.String,java.util.Properties,java.lang.String)
[javac] location: class org.postgresql.jdbc3g.AbstractJdbc3gConnection
[javac]         super(host, port, user, database, info, url);
[javac]         ^
[javac] /home/craig/projects/pgjdbc/org/postgresql/ssl/jdbc3/AbstractJdbc3MakeSSL.java:62: getHostString() is not public in java.net.InetSocketAddress; cannot be accessed from outside package
[javac]         Socket newConnection = factory.createSocket(stream.getSocket(), stream.getAddress().getHostString(), stream.getAddress().getPort(), true);
[javac]                                                                                          ^
[javac] Note: Some input files use or override a deprecated API.
[javac] Note: Recompile with -Xlint:deprecation for details.
[javac] Note: Some input files use unchecked or unsafe operations.
[javac] Note: Recompile with -Xlint:unchecked for details.
[javac] 4 errors

$ java -version
java version "1.5.0_22"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_22-b03)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_22-b03, mixed mode)

$ echo $JAVA_HOME
/home/craig/java/jdk1.5.0_22/

$ ant -version
Apache Ant(TM) version 1.8.3 compiled on February 29 2012

I don't see any explicit support policy in the README, but JDBC3 support implies JDK5 if not JDK4 support.

fix return code when server reports the xid provided does not exist.

Currently the driver returns a code to report a general resource manager error. Change it to return a different code when the xid is not found by the server.
More background to this issue is available in the pgjdbc mailing list.
http://www.postgresql.org/message-id/[email protected]

8.3 support broken; AbstractJdbc2DatabaseMetaData.getColumnPrivileges uses 8.4-only attribute

Tests of master (9d55850) against 8.3 show that it's broken with 8.3. Unsure what broke it or what the oldest working server is yet, just noting this so far. Tested with both JDK5 and JDK7 with same results.

    [junit] Testcase: testColumnPrivileges(org.postgresql.test.jdbc2.DatabaseMetaDataTest):    Caused an ERROR
    [junit] ERROR: column a.attacl does not exist
    [junit]   Position: 38
    [junit] org.postgresql.util.PSQLException: ERROR: column a.attacl does not exist
    [junit]   Position: 38
    [junit]     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    [junit]     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    [junit]     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:546)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:276)
    [junit]     at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getColumnPrivileges(AbstractJdbc2DatabaseMetaData.java:2671)
    [junit]     at org.postgresql.test.jdbc2.DatabaseMetaDataTest.testColumnPrivileges(DatabaseMetaDataTest.java:404)
    [junit] 
    [junit] 
    [junit] Testcase: testGetTimestampWTZ(org.postgresql.test.jdbc2.TimestampTest):     FAILED
    [junit] expected:<2000-07-07 23:00:00.123> but was:<2000-07-07 23:00:00.122999>
    [junit] junit.framework.AssertionFailedError: expected:<2000-07-07 23:00:00.123> but was:<2000-07-07 23:00:00.122999>
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.timestampTestWTZ(TimestampTest.java:434)
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.testGetTimestampWTZ(TimestampTest.java:186)
    [junit] 
    [junit] 
    [junit] Testcase: testSetTimestampWTZ(org.postgresql.test.jdbc2.TimestampTest):     FAILED
    [junit] expected:<2000-07-07 23:00:00.123> but was:<2000-07-07 23:00:00.122999>
    [junit] junit.framework.AssertionFailedError: expected:<2000-07-07 23:00:00.123> but was:<2000-07-07 23:00:00.122999>
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.timestampTestWTZ(TimestampTest.java:434)
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.testSetTimestampWTZ(TimestampTest.java:256)
    [junit] 
    [junit] 
    [junit] Testcase: testGetTimestampWOTZ(org.postgresql.test.jdbc2.TimestampTest):    FAILED
    [junit] null
    [junit] junit.framework.AssertionFailedError
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.timestampTestWOTZ(TimestampTest.java:517)
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.testGetTimestampWOTZ(TimestampTest.java:307)
    [junit] 
    [junit] 
    [junit] Testcase: testSetTimestampWOTZ(org.postgresql.test.jdbc2.TimestampTest):    FAILED
    [junit] null
    [junit] junit.framework.AssertionFailedError
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.timestampTestWOTZ(TimestampTest.java:517)
    [junit]     at org.postgresql.test.jdbc2.TimestampTest.testSetTimestampWOTZ(TimestampTest.java:399)
    [junit] 
    [junit] 
    [junit] Test org.postgresql.test.jdbc2.Jdbc2TestSuite FAILED

Create compat tests against MySQL Connector/J, Oracle JDBC, etc

We need some tests that compare PgJDBC to MySQL's Connector/J and Oracle JDBC (at least). The tests should compile once, then be run against the three different implementations using system properties to control connection and driver info. These tests only need to bother with the latest JDBC revision and should look at commonly implemented extensions - possibly things like support for native arrays (String[], long[], etc) - and at BLOB/CLOB, XML, and other areas of common difference.

Known needed is a test that demonstrates PgJDBC's requirement that BLOBs be accessed with an open transaction, while MySQL doesn't.

Ideally the TCK would do much of this for us, but licensing requirements may limit that.

TODO.

DatabaseMetaData.getProcedureColumns fails

For instance, if you have a function:

CREATE FUNCTION empty.ie3()
RETURNS TABLE (i int, j int) LANGUAGE sql
AS 'SELECT 1, 2';

And you try to call:

ResultSet rs = metaData.getProcedureColumns(
                    con.getCatalog(),
                    "schema_name",
                    "ie3",
                    null);

You'll get the result like that:

stored Procedure name=ie3
procedureCatalog=null
procedureSchema=empty
procedureName=ie3
columnName=i
columnReturn=1
columnDataType=4
columnReturnTypeName=int4
columnPrecision=0
columnByteLength=0
columnScale=0
columnRadix=0
columnNullable=2
columnRemarks=null

stored Procedure name=ie3
procedureCatalog=null
procedureSchema=empty
procedureName=ie3
columnName=j
columnReturn=1
columnDataType=4
columnReturnTypeName=int4
columnPrecision=0
columnByteLength=0
columnScale=0
columnRadix=0
columnNullable=2
columnRemarks=null

Send client-encoding in startup packet not SET

It looks like we may have an issue with client-encoding and pre-auth errors. Per:

http://stackoverflow.com/q/24496757/398670

I'll look into this when time permits, just noting it here for now.

Support NIO non-blocking SSL sockets

It'd be interesting to support non-blocking sockets with Java's newer NIO framework, for the purpose of providing true asynchronous notifications and to allow non-blocking operation.

Anyone looking into this should examine how libpq handles it.

DatabaseMetaData does not handle schemaPattern correctly

According to the interface docs, an empty schemaPattern should retrieve “those without a schema”. While null means "the schemaPattern should not be used to narrow the search”. This same info appears for any schemaPattern in any method of the interface (http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html).

All through AbstractJdbc2DatabaseMetaData.java, anywhere schemaPattern appears, you’ll see code like this:

if (schemaPattern != null && !"".equals(schemaPattern))
{
sql += ...
}

optionally support Unix sockets

Hi.

There seem to be several solutions out there for having UNIX sockets in JAVA:
http://packages.debian.org/sid/libunixsocket-java
https://github.com/mcfunley/juds

IMHO it would be really nice, if pgjdbc optionally supports these.

Cheers,
Chris.

PostgreSQL JDBC bug with XA recovery

Sorry, my fault. I've just copy-pasted SQL from previous topic. We have a
problem with multiple databases, it has nothing to do with users and
privileges.

What do we want, is
SELECT gid FROM pg_prepared_xacts where database = current_database()
in PGXAConnection.recover() function.

As I understand, you agree that it will fix the problem with multiple
databases. Could you please make a fix in official code shortly?

Thanks!

28.02.2013 18:23, Heikki Linnakangas пишет:

On 28.02.2013 16:54, Andrew Frolov wrote:

I've encountered a problem with xa transaction recovery.

PGXAConnection.recover() functions returns all pending XA transactions.
But the problem is, that in general case we cannot recover this
transactions in current connection, PostgreSQL require us to connect to
target database before recovering. This behavior breaks the expectations
of transaction managers. We literally can't perform recovery if we have
a lot of pending xa transactions in different databases in PostgreSQL
cluster.

Maybe it would be good to replace
SELECT gid FROM pg_prepared_xacts
by
SELECT gid FROM pg_prepared_xacts,where owner = current_user ?

I found an old thread with same question, but without an answer.
http://www.postgresql.org/message-id/CAPSK6ngYLaRAy_FGGQqknTPE9FUDCRn32UShxuncWEyD+swD=[e-mail]

There's some confusion here. Both you and the old thread talks about
transactions in multiple databases being the problem, but the error
message, and your suggestion above, suggest that the problem is actually
about permissions. We probably should do:

SELECT gid FROM pg_prepared_xacts where database = current_database()

That should help with the problem with multiple databases. But do you also
have a problem with multiple PostgreSQL users being involved?

• Heikki

PGXAConnection of Driver Version 9.3.1001 does not play nicely with Apache DBCP 1.4

Using the PGXAConnection with Apache DBCP 1.4 results in something like the following:

java.sql.SQLException: Connection does not have a registered XAResource jdbc:postgresql://tng00.pgsql.tng.de:5432/prospero_test?loginTimeout=0&socketTimeout=0&prepareThreshold=5&unknownLength=0&tcpKeepAlive=false&binaryTransfer=false, UserName=prospero_test_user, PostgreSQL Native Driver
        at org.apache.commons.dbcp.managed.TransactionRegistry.getXAResource(TransactionRegistry.java:78)
        at org.apache.commons.dbcp.managed.TransactionContext.setSharedConnection(TransactionContext.java:88)
        at org.apache.commons.dbcp.managed.ManagedConnection.updateTransactionStatus(ManagedConnection.java:131)
        at org.apache.commons.dbcp.managed.ManagedConnection.<init>(ManagedConnection.java:55)
        at org.apache.commons.dbcp.managed.ManagedDataSource.getConnection(ManagedDataSource.java:77)

The problem lies within getConnection() where a new Connection Proxy is returned every time the method is called. Since DBCP uses the Connection Object has a key to a (weak) hash map, however hashCode() and equals() do not always deliver stable data on the returned proxies.

We are currently using a patched version, with the following things added:

public class PGXAConnection // code omitted ...

    private static final Method OBJECT_EQUALS =
            getObjectMethod("equals", Object.class);

    private static final Method OBJECT_HASHCODE =
            getObjectMethod("hashCode");

    // code omitted ,,,

    private static Method getObjectMethod(final String name, final Class... types) {
        try {
            // null 'types' is OK.
            return Object.class.getMethod(name, types);
        } catch (final NoSuchMethodException e) {
            throw new IllegalArgumentException(e);
        }
    }

    // code omitted ...

    private class ConnectionHandler implements InvocationHandler
    {
        // code omitted ...

       // this is my new implementation of the invoke-Method:
        @Override
        public Object invoke(final Object proxy, final Method method, final Object[] args)
        throws Throwable
        {
            if (OBJECT_HASHCODE.equals(method)) {
                // System.out.println("hashCode: " + proxy + " -> " +
                // con.hashCode());
                return con == null ? 0 : con.hashCode();
            }

            if (OBJECT_EQUALS.equals(method)) {
                return equalsInternal(proxy, args[0]);
            }

            // System.out.println("call to method: " + method);

        if (state != STATE_IDLE)
            {
                final String methodName = method.getName();
                if (methodName.equals("commit") ||
                    methodName.equals("rollback") ||
                    methodName.equals("setSavePoint") ||
                    (methodName.equals("setAutoCommit") && ((Boolean) args[0]).booleanValue()))
                {
            throw new PSQLException(GT.tr("Transaction control methods setAutoCommit(true), commit, rollback and setSavePoint not allowed while an XA transaction is active."),
                        PSQLState.OBJECT_NOT_IN_STATE);
                }
            }
            try {
                return method.invoke(con, args);
            } catch (final InvocationTargetException ex) {
                throw ex.getTargetException();
            }
        }

        // this will provide the actual internal comparison
        private boolean equalsInternal(final Object proxy, final Object other) {
            // System.out.println("equalsInternal: " + proxy + " ?= " + other);
            if (other == null) {
                return false;
            }
            if (Proxy.isProxyClass(other.getClass())) {
                final InvocationHandler otherHandler = Proxy.getInvocationHandler(other);
                if (otherHandler instanceof ConnectionHandler) {
                    final ConnectionHandler otherConnectionHandler = (ConnectionHandler) otherHandler;
                    return con.equals(otherConnectionHandler.con);
                } else {
                    return false;
                }
            } else {
                // other is not a proxied object
                return con.equals(other);
            }
        }

        // code omitted ...
    }

    // code omitted ...

}

I know I should better provide patch or pull-request, but my editor currently does a lot of automatic code-formatting resulting in a patch that nearly changes the entire file.

I hope I could help anyways.

Regards
Carsten

MATERIALIZED VIEWs not shown when retrieving schemas

I am running a Mondrian OLAP Server in combination with PostgreSQL 9.3, using postgresql-9.3-1101.jdbc4.jar. Mondrian cannot retrieve informations about existing MATIERLIAZED VIEWs. It seems to be pgjdbc is not working correctly. I checked my PostgreSQL logs and found following query, assuming that this statement is part of pgjdbc:

SELECT 
    NULL AS TABLE_CAT, 
    n.nspname AS TABLE_SCHEM, 
    c.relname AS TABLE_NAME,  
    CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  
        WHEN true THEN
            CASE  
                WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
                    CASE c.relkind
                        WHEN 'r' THEN 'SYSTEM TABLE'   
                        WHEN 'v' THEN 'SYSTEM VIEW'   
                        WHEN 'i' THEN 'SYSTEM INDEX'   
                        ELSE NULL  
                    END 
                WHEN n.nspname = 'pg_toast' THEN
                    CASE c.relkind   
                        WHEN 'r' THEN 'SYSTEM TOAST TABLE'   
                        WHEN 'i' THEN 'SYSTEM TOAST INDEX'
                        ELSE NULL
                    END 
                ELSE
                    CASE c.relkind
                        WHEN 'r' THEN 'TEMPORARY TABLE'   
                        WHEN 'i' THEN 'TEMPORARY INDEX'   
                        WHEN 'S' THEN 'TEMPORARY SEQUENCE'   
                        WHEN 'v' THEN 'TEMPORARY VIEW'
                        ELSE NULL   
                    END  
            END  
        WHEN false THEN
            CASE c.relkind  
                WHEN 'r' THEN 'TABLE'  
                WHEN 'i' THEN 'INDEX'  
                WHEN 'S' THEN 'SEQUENCE'  
                WHEN 'v' THEN 'VIEW'  
                WHEN 'c' THEN 'TYPE'  
                WHEN 'f' THEN 'FOREIGN TABLE'  
                WHEN 'm' THEN 'MATERIALIZED VIEW'  
                ELSE NULL
            END  
        ELSE NULL 
    END  
        AS TABLE_TYPE,
    d.description AS REMARKS 

FROM
    pg_catalog.pg_namespace n, pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  
    LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  
    LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') 

WHERE
    c.relnamespace = n.oid  
    AND c.relname LIKE '%' 
    AND
    (
        false
        OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )
        OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' )
    ) 

ORDER BY
    TABLE_TYPE,
    TABLE_SCHEM,
    TABLE_NAME

The causing problem is the last WHERE condition, limiting the result set to tables and (regular) views only. Here is the correct Statement, including materialized views and foreign tables into the result set:

SELECT 
    NULL AS TABLE_CAT, 
    n.nspname AS TABLE_SCHEM, 
    c.relname AS TABLE_NAME,  
    CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  
        WHEN true THEN
            CASE  
                WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN
                    CASE c.relkind
                        WHEN 'r' THEN 'SYSTEM TABLE'   
                        WHEN 'v' THEN 'SYSTEM VIEW'   
                        WHEN 'i' THEN 'SYSTEM INDEX'   
                        ELSE NULL  
                    END 
                WHEN n.nspname = 'pg_toast' THEN
                    CASE c.relkind   
                        WHEN 'r' THEN 'SYSTEM TOAST TABLE'   
                        WHEN 'i' THEN 'SYSTEM TOAST INDEX'
                        ELSE NULL
                    END 
                ELSE
                    CASE c.relkind
                        WHEN 'r' THEN 'TEMPORARY TABLE'   
                        WHEN 'i' THEN 'TEMPORARY INDEX'   
                        WHEN 'S' THEN 'TEMPORARY SEQUENCE'   
                        WHEN 'v' THEN 'TEMPORARY VIEW'
                        ELSE NULL   
                    END  
            END  
        WHEN false THEN
            CASE c.relkind  
                WHEN 'r' THEN 'TABLE'  
                WHEN 'i' THEN 'INDEX'  
                WHEN 'S' THEN 'SEQUENCE'  
                WHEN 'v' THEN 'VIEW'  
                WHEN 'c' THEN 'TYPE'  
                WHEN 'f' THEN 'FOREIGN TABLE'  
                WHEN 'm' THEN 'MATERIALIZED VIEW'  
                ELSE NULL
            END  
        ELSE NULL 
    END  
        AS TABLE_TYPE,
    d.description AS REMARKS,
    c.relkind

FROM
    pg_catalog.pg_namespace n, pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  
    LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  
    LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') 

WHERE
    c.relnamespace = n.oid
    AND c.relname LIKE '%'
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND c.relkind IN ('r', 'v', 'm', 'f')

ORDER BY
    TABLE_TYPE,
    TABLE_SCHEM,
    TABLE_NAME

Since I'm not familiar to git, could you please bring the code to master/branches/patches? Or am I wrong and this code is produced by Mondrian?

Thank you for your great work.

PGXAConnection.commitOnePhase() does not rollback at SQLException

According to the JTA documentation [1] if an one phase transaction fails with a XA_RM* exception then is must roll back and release the transaction. The PGXAConnection.commitOnePhase() method simply throws an XAException and leaves the transaction open.

[1] http://docs.oracle.com/javase/6/docs/api/javax/transaction/xa/XAResource.html#commit%28javax.transaction.xa.Xid,%20boolean%29

NonValidatingFactory missing from JDBC3 driver builds

Hi

A customer reported that they were unable to use the NonValidatingFactory with newer driver releases. Using a JDBC URL like jdbc:postgresql://ipaddress:port/dbname?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory would fail with an exception such as:

Caused by: java.lang.ClassNotFoundException: org.postgresql.ssl.NonValidatingFactory

This can be confirmed by compiling the following test program and running it against the JDBC3 driver. It'll fail on the JDBC3 driver but work fine with the same JDBC4 driver release.

import java.sql.Connection;
import java.sql.DriverManager;

public class JDBCConnect {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection con = DriverManager.getConnection(
                             "jdbc:postgresql:test?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory",
                             "test",
                             "password");
        System.out.println("OK!");

    }   
}

Result:

$ javac JDBCConnect.java && java -cp postgresql-9.2-1003.jdbc4.jar: JDBCConnect
jar:file:/home/craig/Downloads/postgresql-9.2-1003.jdbc4.jar!/org/postgresql/ssl/NonValidatingFactory.class
OK!

$ javac JDBCConnect.java && java -cp postgresql-9.2-1003.jdbc3.jar: JDBCConnect
Exception in thread "main" org.postgresql.util.PSQLException: The SSLSocketFactory class provided org.postgresql.ssl.NonValidatingFactory could not be instantiated.
        at org.postgresql.ssl.jdbc3.AbstractJdbc3MakeSSL.convert(AbstractJdbc3MakeSSL.java:58)
        .... elided ....
Caused by: java.lang.ClassNotFoundException: org.postgresql.ssl.NonValidatingFactory
        at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
        .... elided ...

It looks like it's an issue with build.xml's target selection. I'm looking into it now.

setNull, setString, setObject may fail if a specified type cannot be transfered in a binary mode

ParameterList.setNull always uses a binary transfer mode.

public void setNull(int index, int oid) throws SQLException {
    bind(index, NULL_OBJECT, oid, BINARY);
}

However there are some types that does not support it.

PreparedStatement ps = c.prepareStatement("SELECT nlevel(?)");
ps.setObject(1, null);
ps.execute();

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: no binary input function available for type ltree
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:562)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
    at org.test.Main.main(Main.java:82)

It's a regression from 9.1 version.

NUMERIC results of calculations have no/wrong scale and precision in metadata

See:

http://archives.postgresql.org/message-id/CAFEjsq4Qc=nf=FO0iAGWZd7oAtxt11pnt7eUsxv1T_wpe=[e-mail]

http://archives.postgresql.org/message-id/[email protected]

Integrate maven-ant-tasks

(TODO / note-to-self)

Since so many people get PgJDBC from maven, make it easier to publish to there by integrating maven-ant-tasks.

getGeneratedKeys returns all columns for Statement.RETURN_GENERATED_KEYS

In version 9.3-1100 (and previous versions) the getGeneratedKeys() call returns all columns, instead of just generated ones. This behaviors breaks ORMs which parse result sets expecting only the (usual) primary key columns to be returned.

Failure to parse data in fields of type 'money'

We are trying to read some records out of a legacy enterprise DB we have inherited, which contain fields encoded with the Postgres 'money' type. However it appears the Postgres JDBC driver is trying to parse these as some kind of double, or boxed Java alternative like BigDecimal. When it hits the currency symbol, the driver bombs out with an exception like the following:

org.postgresql.util.PSQLException: Bad value for type double : £55.76
 at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDouble(AbstractJdbc2ResultSet.java:3038)
 at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getDouble(AbstractJdbc2ResultSet.java:2382)
 at [our code that called it]

We played around with switching on column type to see what the driver was doing internally, with some (quick and dirty) code like this:

switch (columns.getColumnType(i)) {
...
case Types.DOUBLE:
  data.get(i+n).add(record.getDouble(j+1));
  break;
...

Since it ended up going into the Types.DOUBLE case, we can only assume the driver has decided to parse money as a double (or boxed equivalent).

We tried dollars as currency symbols, after seeing an earlier bug about Postgres only recognising money values if they were in USD, but this didn't work either.

We've tried this on versions 9.1 and 9.2 of the driver (9.3 hasn't hit Maven yet so we haven't been able to get this into our project).

Postgres DB version is 9.3.1 build 1600.
LC_MONETARY value is English_United Kingdom.1252 - this was just the default on our system.

(What we'd really like to do is disable money formatting altogether, or convert the field type to something that's not money, but since it's a legacy DB we don't have much wiggle room. And it's a separate problem to the one described above.)

PgJDBC attempts to parse server version

PgJDBC is attempting to parse server_version or version(), instead of using server_version_num.

This causes breakage if Pg is compiled --with-extra-version or on beta1 releases etc.

See http://www.postgresql.org/message-id/[email protected] .

Assigning to self.

ResultSet metadata for a prepared statement missing getTableName etc

See http://archives.postgresql.org/message-id/CAFEjsq6Rd1DOQVM3vWdbfZ_h5=[e-mail]

Driver attempts to send POLYGONs in binary format even when only text format is supported

The issue with analysis was first published in the relevant mailing list, but got no attention, which is why I'm opening a ticket here.

For quite some time, we were working with JDBC driver "postgresql:postgresql:9.1-902.jdbc4" and it worked quite fine with both PostgreSQL 8.4 and 9.1 (various versions of both on development and production servers - it worked fine everywhere).

A couple of days ago I've found that the newer driver has improved support for HSTORE data type, and upgraded to the driver "org.postgresql:postgresql:9.2-1003-jdbc4"

Unfortunately, after the upgrade, several unit tests (those saving polygons) started to fail with the following error message:

Caused by: org.postgresql.util.PSQLException: ERROR: no binary input function available for type geography
Call: INSERT INTO location_polygon (id, center_surface, polygon, location_id) VALUES (?, ?, ?, ?)

I've prepared a simple unit test (PostGIS 1.5.2 + PostgreSQL JDBC driver) that reproduces the problem with the new JDBC driver, and works fine with the old driver.

The code of the test looks like the following:

    @Test
    public void testSavePolygons() throws Exception {
            final String url = "jdbc:postgresql://localhost/tms_test?user=xxxxxxxx&password=xxxxxxxx";
            final Connection conn = DriverManager.getConnection(url);

            final String s = "INSERT INTO location_polygon ( id, location_id, polygon, center_surface ) VALUES ( ?, ?, ?, ? )";
            final PreparedStatement ps = conn.prepareStatement( s );
            ps.setLong( 1, 72147725131120643l );
            ps.setNull( 2, Types.OTHER );
            final PGpolygon pg = new PGpolygon();
            pg.setValue( "((30,30),(30,30.001),(30.001,30.001),(30.001,30),(30,30))" );
            final PGgeometry pgg = new PGgeometry();
            pgg.setGeometry( convertPolygon( pg ) );
            pgg.setType( "geometry" );
            ps.setObject( 3, pgg );
            ps.setNull( 4, Types.OTHER );

            final int ret = ps.executeUpdate();

            logger.info( "ret = {}", ret );
    }

Some more research, comparing that specific query between the two driver versions:

I stop in the function: org.postgresql.core.v3.QueryExecutorImpl.sendQuery()

In the new driver, I see the parameters:

paramTypes = [20, 0, 282933, 0] - correct types, with OID for geometry = 282933

paramValues[0] = 8 bytes containing correct LONG
paramValues[1] = some singleton object representing null
paramValues[2] = "POLYGON((30 30,30.001 30,30.001 30.001,30 30.001,30 30))" - string
paramValues[3] = some singleton object representing null

protoConnection.useBinaryForOIDs = [1016, 1184, 1021, 1022, 2950, 1009, 1015, 17, 1083, 1082, 700, 701, 21, 1266, 1005, 20, 1007, 23, 1114, 600, 603]

Note that 282933 is not in this list.

With the old driver, it is very similar, except for the first LONG parameter being passed as a string, and no such useBinaryForOIDs array existing - the complete implementation of protoConnection differs.

If I interpret the error message correctly (ERROR: no binary input function available for type geography), there is a bug in the new driver - it does not look at the useBinaryForOIDs array where it should. The fallback to the text format is not working correctly.

getIndexInfo() returns COLUMN_NAME as quoted string

In the process of updating the Pg JDBC driver used in GeoServer [1], I've run across a difference of behavior in the latest version: AbstractJdbc2DatabaseMetaData.getIndexInfo() returns COLUMN_NAME as a quoted string.
I don't know if the use of a quoted string is correct but I've dig up this old changelog on this topic:

Version 8.3-dev601 (2007-07-31): 
  * fix Don't return quotes around identifiers in the results of DatabaseMetaData.getIndexInfo even if they would require quoting in SQL. Committed by jurka. Thanks to Andrei Badea.

The corresponding commit is: 4db623c
That past behavior has been reverted in commit 1d51603, through the use of pg_catalog.pg_get_indexdef() instead of pg_catalog.pg_attribute.attname previously.
What's strange is that the commit message mentions a change justified by Pg 9.0, whereas the code uses connection.haveMinimumServerVersion("8.3").

So is it a regression? What does the JDBC spec says in regard to columns as quoted strings?

[1] https://jira.codehaus.org/browse/GEOT-4636