openshift-cartridges/openshift-wildfly-cartridge — pull request #29 by smoyer64 (2014-11-01 14:55:30+00:00)
Title: OpenShift Wildfly cartridge PostgreSQL JDBC driver is incompatible with the OpenShift PostgreSQL server cartridge

# OpenShift Wildfly cartridge PostgreSQL JDBC driver is incompatible with the OpenShift PostgreSQL server cartridge

## Overview

The PostgreSQL JDBC driver provided with the 8.1.0.Final release of the OpenShift Wildfly cartridge is incompatible with the PostgreSQL server provided by the OpenShift PostgreSQL cartridge. The provided 8.4-701 driver expects the PostgreSQL server to provide columns with the bytea type to be returned in the legacy "escape" format, but the 9.2.8-90208 server returns these columns in the new (but default) "hex" format.

## Symptoms

Binary data stored in PostgreSQL bytea columns will be approximately (within one or two bytes) twice as large as their original sizes and the data will appear to be corrupted. In reality, the output will be the hex representation of the except for the first few stored characters.

For example, the first four bytes of a PNG image file are "/PNG" (where the / is 0x89 with my character encoding). These four bytes are stored in the PostgreSQL bytea column as "\x89504e47". A PostgreSQL 9.x JDBC driver will correctly return "/PNG" while an affected <9.x PostgreSQL JDBC driver will return "I504e47".

## Known Workarounds

### 1. Patch the Wildfly PostgreSQL (recommended)

Patch files as well as detailed instructions for applying them to your OpenShift Wildfly application can be found [here](https://github.com/selesy/openshift-wildfly-cartridge/releases/tag/8.1.0.Final-Postgresql-JDBC-Driver-Module-Patch-9.3-1102).

### 2. Programmatically (acceptable)

This error can be mitigated programmatically by instructing the PostgreSQL server to provide the JDBC client with the needed "escape" format. This is done within the application's JPA PersistenceContextshould and should be executed after the PersistenceContext is initialized but before queries that return bytea columns are executed. The following code provides the query that should be executed but it's placement within your application will be dependent upon it's start-up sequence.

```
public class MyClass {
...
@PersistenceContext
private EntityManager entitymanager;
...
private void fixByteaOutput() {
Query query = entityManager.createNativeQuery("set bytea_output TO 'escape'");
query.executeUpdate();
}
...
}
```

### 3. JBoss deployment structure (not recommended)

Please reconsider workaround 1 or 2 ... if you're going to use OpenShift (or any other PaaS), you should avoid configurations that might make your application incompatible with different versions of a container (or different containers).

In any case, this bug can be temporarily avoided by providing a jboss_deployment_structure.xml file that excludes the broken driver. You'll then need to provide the correct version of the driver within your application and update the standalone.xml file on each server to recognize it. This method is beyond the scope of this pull request and should only be attempted after understanding how a JavaEE application can override the default server environment ([here](http://middlewaremagic.com/jboss/?p=1933) is one starting point).

## Remedy

This pull request replaces the existing PostgreSQL JDBC driver with the latest release (9.3-1102) This version is backwards compatible with previous drivers and shouldn't cause working applications to break.