Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-19318

Dimension lookup / update.0 - Caused by: org.postgresql.util.PSQLException: ERROR: Date / time field value out of range: "2021-09-31 15:00: 00.313 + 00 "

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Unknown
    • Resolution: Not a Bug
    • Affects Version/s: 9.0.0 GA, 9.1.0 GA, 9.2.0 GA
    • Fix Version/s: Backlog
    • Component/s: Step
    • Labels:
      None
    • Environment:
      java version "1.8.0_221"
      Java(TM) SE Runtime Environment (build 1.8.0_221-b11)
      Java HotSpot(TM) 64-Bit Server VM (build 25.221-b11, mixed mode)
    • Story Points:
      0
    • PDI Sub-component:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Operating System/s:
      CentOS 7.x, Windows 10
    • Steps to Reproduce:
      Hide

      In my PDI transfomation I do insert a lot rows into DWH (on PostgreSQL 12) across Dimension lookup/update steps.
      I run that transformation in month with 30 days (February, April, June, September, etc) and having an error 'org.postgresql.util.PSQLException: ERROR: Date / time field value out of range: "2021-%month_num%-31 HH:MM:SS"'
      Example of %month_num% is 09 (september).

      But in september only 30 days, not 31...

      All data is correct.

      This error triggered on "big" inserts - over 100к rows.

      Older versions of PDI (<8.3) do not have this error.

      Show
      In my PDI transfomation I do insert a lot rows into DWH (on PostgreSQL 12) across Dimension lookup/update steps. I run that transformation in month with 30 days (February, April, June, September, etc) and having an error 'org.postgresql.util.PSQLException: ERROR: Date / time field value out of range: "2021-%month_num%-31 HH:MM:SS"' Example of %month_num% is 09 (september). But in september only 30 days, not 31... All data is correct. This error triggered on "big" inserts - over 100к rows. Older versions of PDI (<8.3) do not have this error.
    • Release Version:
      9.2 GA

      Description

      2021/09/06 15:58:54 - Dimension lookup / update.0 - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy): org.pentaho. di.core.exception.KettleDatabaseException:
      2021/09/06 15:58:54 - Dimension lookup / update.0 - Error looking up row in database
      2021/09/06 15:58:54 - Dimension lookup / update.0 - ERROR: current transaction aborted, commands to the end of the transaction block are ignored
      2021/09/06 15:58:54 - Dimension lookup / update.0 -
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.core.database.Database.getLookup (Database.java:3114)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.core.database.Database.getLookup (Database.java:3093)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.core.database.Database.getLookup (Database.java:3089)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.lookupValues ​​(DimensionLookup.java:449)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow (DimensionLookup.java:232)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.trans.step.RunThread.run (RunThread.java:62)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at java.lang.Thread.run (Unknown Source)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - Caused by: org.postgresql.util.PSQLException: ERROR: current transaction aborted, commands to end of transaction block are ignored
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2440)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.core.v3.QueryExecutorImpl.processResults (QueryExecutorImpl.java:2183)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.core.v3.QueryExecutorImpl.execute (QueryExecutorImpl.java:308)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.jdbc.PgStatement.executeInternal (PgStatement.java:441)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.jdbc.PgStatement.execute (PgStatement.java:365)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags (PgPreparedStatement.java:143)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.postgresql.jdbc.PgPreparedStatement.executeQuery (PgPreparedStatement.java:106)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - at org.pentaho.di.core.database.Database.getLookup (Database.java:3099)
      2021/09/06 15:58:54 - Dimension lookup / update.0 - ... 6 more
      2021/09/06 15:58:54 - Dimension lookup / update.0 - Caused by: org.postgresql.util.PSQLException: ERROR: Date / time field value out of range: "2021-09-31 15:00: 00.313 + 00 "
      2021/09/06 15:58:54 - Dimension lookup / update.0 - ... 14 more

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            Sergeev Jury
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: