Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Java Heap Space Issue in mappings with PostgreSQL tables in source

    Symptom

    When loading data from Postgresql tables you can sometimes experience "Java Heap Space" error during the execution of your process. You may also notice that executions with Postgresql tables in source take a lot of runtime memory.

     

    Solution

    This is caused by the default behvaiour of the Postgresql jdbc driver, that loads all the source records into the runtimes memory (as oppposed to standard behaviour which consists of loading the lines in batches -> the number of lines in a batch is configured in "SQL Fetch Size" option) as explained in the postgresql doc :

    https://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

    To force the driver to respect the fetchSize declared in Stambia templates you might try to switch into transactional mode (in which the fetchSize is respected).

    If the template does not allow transactionalMode or for some reason cannot use it, in the Designer S19.0.15 and runtime S17.6.4 we have changed the way the data is selected on postgresql tables to force the driver to send the data in batches. So if you encounter the problem described above you should make sure to develop your mapping with Designer 19.0.15 or newer and run the developped process with runtime 17.6.4 or newer.

     

    Error "invalid byte sequence... 0x00" when loading data into Postgresql

      Symptom

      When executing a mapping or a query which loads data into a Postgresql database, the following error may occur if source data contains a NUL character (we are talking about the 0x00 value, not the "null" which means no-value)

      org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00
          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:405)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893)
          at com.indy.engine.actionCodes.JdbcActionCodeI.a(SourceFile:516)
          at com.indy.engine.actionCodes.JdbcActionCodeI.finalizeDirectBindedFetch(SourceFile:541)
          at com.indy.engine.action.common.ActionCodeTypeI.a(SourceFile:1111)
          at com.indy.engine.action.common.ActionCodeTypeI.executeDirectBindedCode(SourceFile:1322)
          at com.indy.engine.action.common.ActionCodeTypeI.executeBindedCode(SourceFile:1448)
          at com.indy.engine.action.common.ActionCodeTypeI.executeCode(SourceFile:1505)
          at com.indy.engine.action.common.ActionCodeTypeI.run(SourceFile:1666)
          at java.lang.Thread.run(Thread.java:748)

      Solution

      Postgresql cannot load the NUL character. You need to process the source data in order to remove NUL characters.

      For example, you can add a Stage between the source and the Postgresql target, and apply a REPLACE() function to the target field.

      Example with an H2 stage:

      REPLACE(source.column, char(0), '')

      Knowledge Base

      Suggest a new Article!