Bug 5315

Summary: Broken PostgreSQL Support in SE mode
Product: jbatch Reporter: mbogner2
Component: RIAssignee: cvignola
Status: RESOLVED MOVED    
Severity: major CC: htavarez, issues, kewlmain, lberteau, radcortez, ScottKurz, thomas_haines
Priority: P5    
Version: 1   
Target Milestone: ---   
Hardware: PC   
OS: Windows   
Whiteboard: RI_only

Description mbogner2 2013-08-21 10:40:36 UTC
Hello,

I'm trying to get the SE RI running with an up2date installation of postgresql. I've already imported the ddl [3] into public schema and configured the database connection.

The exception is thrown when executing JDBCPersistenceManagerImpl.java:386

PreparedStatement ps = null;
ps = connection.prepareStatement("SET SCHEMA ?");
ps.setString(1, schema);
ps.executeUpdate();

My connection is configured correctly and the SQL statement would be "SET SCHEMA 'public'" as my connetion-config looks like this:
JDBC_DRIVER=org.postgresql.Driver
JDBC_URL=jdbc\:postgresql\://localhost/jsr352
DB_USER=jsr352
DB_PWD=jsr352
DB_SCHEMA=public

Executing the statement on the command line works, but executing it with plain JDBC results in the same exception as I get it in the RI.

public static void main(String[] args) throws SQLException {
        final PreparedStatement stmt = DriverManager.getConnection(
          "jdbc:postgresql://localhost/jsr352", "jsr352","jsr352")
          .prepareStatement("SET SCHEMA ?");
        stmt.setString(1, "public");
        stmt.executeUpdate();
        stmt.close();
}

results in the exception as stated under [1].

The RI tries to execute this statement in class JDBCPersistenceManagerImpl.java line 386 which results in the exception as stated under [2].

As this statement is executed every time a connection is opened I don't know how to use the RI with PostgreSQL without changing the source. I think the statement SET SCHEMA should be avoided in general.

Regards,
Manuel

[1]

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 12
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
	at test.Main.main(Main.java:21)

[2]

Exception in thread "main" javax.batch.operations.JobStartException: com.ibm.jbatch.container.exception.PersistenceException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 12
	at com.ibm.jbatch.container.api.impl.JobOperatorImpl.start(JobOperatorImpl.java:90)
	at at.itsv.sea.jsr352se.util.BatchExecutionUtil.submitJob(BatchExecutionUtil.java:14)
	at at.itsv.sea.jsr352se.ExampleBatch.main(ExampleBatch.java:33)
Caused by: com.ibm.jbatch.container.exception.PersistenceException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 12
	at com.ibm.jbatch.container.services.impl.JDBCPersistenceManagerImpl.createJobInstance(JDBCPersistenceManagerImpl.java:1712)
	at com.ibm.jbatch.container.jobinstance.JobExecutionHelper.getNewJobInstance(JobExecutionHelper.java:89)
	at com.ibm.jbatch.container.jobinstance.JobExecutionHelper.startJob(JobExecutionHelper.java:120)
	at com.ibm.jbatch.container.impl.BatchKernelImpl.startJob(BatchKernelImpl.java:123)
	at com.ibm.jbatch.container.api.impl.JobOperatorImpl.startInternal(JobOperatorImpl.java:121)
	at com.ibm.jbatch.container.api.impl.JobOperatorImpl.start(JobOperatorImpl.java:86)
	... 2 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 12
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
	at com.ibm.jbatch.container.services.impl.JDBCPersistenceManagerImpl.setSchemaOnConnection(JDBCPersistenceManagerImpl.java:386)
	at com.ibm.jbatch.container.services.impl.JDBCPersistenceManagerImpl.getConnection(JDBCPersistenceManagerImpl.java:323)
	at com.ibm.jbatch.container.services.impl.JDBCPersistenceManagerImpl.createJobInstance(JDBCPersistenceManagerImpl.java:1700)
	... 7 more

[3]

DROP TABLE IF EXISTS JOBSTATUS;
DROP TABLE IF EXISTS STEPSTATUS;
DROP TABLE IF EXISTS CHECKPOINTDATA;
DROP TABLE IF EXISTS JOBINSTANCEDATA;
DROP TABLE IF EXISTS EXECUTIONINSTANCEDATA;
DROP TABLE IF EXISTS STEPEXECUTIONINSTANCEDATA;

CREATE TABLE JOBINSTANCEDATA(
  jobinstanceid		serial not null PRIMARY KEY,
  name		character varying (512), 
  apptag VARCHAR(512)
);

CREATE TABLE EXECUTIONINSTANCEDATA(
  jobexecid		serial not null PRIMARY KEY,
  jobinstanceid	bigint not null REFERENCES JOBINSTANCEDATA (jobinstanceid),
  createtime	timestamp,
  starttime		timestamp,
  endtime		timestamp,
  updatetime	timestamp,
  parameters	bytea,
  batchstatus		character varying (512),
  exitstatus		character varying (512)
);
  
CREATE TABLE STEPEXECUTIONINSTANCEDATA(
	stepexecid			serial not null PRIMARY KEY,
	jobexecid			bigint not null REFERENCES EXECUTIONINSTANCEDATA (jobexecid),
	batchstatus         character varying (512),
    exitstatus			character varying (512),
    stepname			character varying (512),
	readcount			integer,
	writecount			integer,
	commitcount         integer,
	rollbackcount		integer,
	readskipcount		integer,
	processskipcount	integer,
	filtercount			integer,
	writeskipcount		integer,
	startTime           timestamp,
	endTime             timestamp,
	persistentData		bytea
); 

CREATE TABLE JOBSTATUS (
  id		bigint not null REFERENCES JOBINSTANCEDATA (jobinstanceid),
  obj		bytea
);

CREATE TABLE STEPSTATUS(
  id		bigint not null REFERENCES STEPEXECUTIONINSTANCEDATA (stepexecid),
  obj		bytea
);

CREATE TABLE CHECKPOINTDATA(
  id		character varying (512),
  obj		bytea
);
Comment 1 mbogner2 2013-08-21 10:55:29 UTC
Strange behavior. I wrote two unit tests to get behind this bug. The first one uses PreparedStatement and fails as expected. But using normal Statement as done in the second test works. Bad practice, but works.

    @Test
    public void testSetSchemaPrepared() throws SQLException {
        final PreparedStatement stmt = con.prepareStatement("SET SCHEMA ?");
        stmt.setString(1, "");
        try {
            stmt.executeUpdate();
            fail("this doesnt work, doesnt it?");
        } catch (final SQLException exc) {
            // expected
        } finally {
            stmt.close();
        }
    }

    @Test
    public void testSetSchema() throws SQLException {
        final Statement stmt = con.createStatement();
        stmt.execute("SET SCHEMA 'public'");
        stmt.close();
    }
Comment 2 mbogner2 2013-08-21 11:57:11 UTC
@PreparedStatement-Test:
stmt.setString(1, "");
or
stmt.setString(1, "public");
gives the same result...
Comment 3 thomas_haines 2013-08-22 12:15:45 UTC
Hi, this same issue / same line also fails if you try to run SE RI using MySQL (error when SET SCHEMA command is called from JDBCPersistenceManagerImpl).

I traced it through in the source, but couldn't see a way to fix it without branching & adding logic to skip sending the command when the JDBC_DRIVER is MySQL.
Comment 4 htavarez 2013-08-27 19:55:08 UTC
What release version of PostgreSQL are you using?
Comment 5 mbogner2 2013-08-27 23:37:42 UTC
(In reply to htavarez from comment #4)
> What release version of PostgreSQL are you using?

I'm using the latest release. 9.2.4
Comment 6 ScottKurz 2013-08-29 16:19:54 UTC
So it seems from here:

http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schema-when-connecting-to-postgres-with-jdbc

the "schema" can be specified on the conn URL.

That suggests we could have a new property in batch-config.properties:
  SET_DEFAULT_SCHEMA  (defaults to 'true')

If 'false', we would simply not do this SET SCHEMA. 

That works fine for "SE mode", where we use the conn URL.   

That leaves "EE mode", where we're using a DataSource.   If anyone is familiar with these please let us know, otherwise I'm inclined to say we'll deal with that if/when we need to, and go ahead and make the above change.

Sound reasonable?
Comment 7 mbogner2 2013-08-29 19:48:14 UTC
(In reply to ScottKurz from comment #6)
> So it seems from here:
> 
> http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schema-
> when-connecting-to-postgres-with-jdbc
> 
> the "schema" can be specified on the conn URL.
> 
> That suggests we could have a new property in batch-config.properties:
>   SET_DEFAULT_SCHEMA  (defaults to 'true')
> 
> If 'false', we would simply not do this SET SCHEMA. 
> 
> That works fine for "SE mode", where we use the conn URL.   
> 
> That leaves "EE mode", where we're using a DataSource.   If anyone is
> familiar with these please let us know, otherwise I'm inclined to say we'll
> deal with that if/when we need to, and go ahead and make the above change.
> 
> Sound reasonable?

Adding the default schema to the connection url has to be supported by the jdbc driver. The article doesn't really say that this feature is supported by the latest postgresql driver. So I don't think this is a very generic solution anyway. Further having a flag to skip this configuration removes the functionality to set a custom schema in the application and leaves you alone with your database config.

In my opinion the correct solution would be to prepend the configured schema name to all table names in the queries instead of setting a default schema for the whole connection. Would this be possible too?
Comment 8 thomas_haines 2013-09-18 01:27:46 UTC
> So I don't think this is a very generic solution anyway. 
> Further having a flag to skip this configuration removes the functionality 
> to set a custom schema in the application and leaves you alone with your database config.

To include the flexibility for all databases, would one solution be to adopt the suggested SET_DEFAULT_SCHEMA [true/false] option in batch-config.properties, 
plus include an additional ‘DB_INIT_STATEMENT‘ option in batch-config.properties. If specified, the DB_INIT_STATEMENT statement is run after the connection is established.

For MySQL and many other drivers, the database name can be specified in the connection URL.  For special cases, you could use the DB_INIT_STATEMENT, e.g. in Postgres if you did not want to use the connection url, you could use:

SET_DEFAULT_SCHEMA=FALSE
DB_INIT_STATEMENT=SET search_path TO jbatch

Perhaps this would balance simplicity with generic support for any jdbc database (between connectionUrl and in rare cases a DB init statement) ?
Comment 9 ScottKurz 2014-12-18 15:25:02 UTC
Some related observations noted with:
https://github.com/WASdev/standards.jsr352.jbatch/issues/23

Note I'd be happy to consider special-casing MySQL and PostgreSQL like we did with Oracle as a tactical solution.  Also there's the start of an effort to make the persistence service truly pluggable and extensible
https://github.com/WASdev/standards.jsr352.jbatch/issues/11
Comment 10 ScottKurz 2015-09-01 20:51:17 UTC
This discussion has moved to:
https://github.com/WASdev/standards.jsr352.jbatch/issues/23.

Closing to clean up Bugzilla.