Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Time Out Issue : org.postgresql.util.PSQLException: An I/O error occurred while sendi

  1. #1

    Default Time Out Issue : org.postgresql.util.PSQLException: An I/O error occurred while sendi

    Hello Folks,

    I have a pentaho report which is using data source as PDI transformation, This pdi transformation is combining the data from postgressql and mongoDB. it was working fine with a less amount of data but as data grows it's throwing a timeout issue in postgres. I have also changed tcp keep alive parameter in postgres sql

    We are using pentaho 5.4 and pdi 5.4 and postgres driver is 9.4.

    Here with i have attached log file also :

    2016-12-12 11:04:33,739 INFO [org.pentaho.di] 2016/12/12 11:04:33 - tra_mongo_weekly_test - Dispatching started for transformation [tra_mongo_weekly_test]
    2016-12-12 11:04:33,834 INFO [org.pentaho.di] 2016/12/12 11:04:33 - MongoDB Input-User login data.0 - Normal authentication for user reports
    2016-12-12 11:04:33,949 INFO [org.pentaho.di] 2016/12/12 11:04:33 - MongoDB Input-User login data.0 - Configuring connection with read preference: primary
    2016-12-12 11:04:33,949 INFO [org.pentaho.di] 2016/12/12 11:04:33 - MongoDB Input-User login data.0 - No read preference tag sets defined
    2016-12-12 11:04:33,950 INFO [org.pentaho.di] 2016/12/12 11:04:33 - MongoDB Input-User login data.0 - Configuring connection with default write concern - w = 1, wTimeout: 0, journaled = false
    2016-12-12 11:04:37,118 INFO [org.pentaho.di] 2016/12/12 11:04:37 - MongoDB Input-User login data.0 - Query pulled data from: prdmng001.northeurope.cloudapp.azure.com/13.69.253.248:27017
    2016-12-12 11:04:40,678 INFO [org.pentaho.di] 2016/12/12 11:04:40 - user_detail.0 - Finished reading query, closing connection.
    2016-12-12 11:04:40,679 INFO [org.pentaho.di] 2016/12/12 11:04:40 - user_detail.0 - Finished processing (I=6960, O=0, R=0, W=6960, U=0, E=0)
    2016-12-12 11:17:03,802 INFO [org.pentaho.di] 2016/12/12 11:17:03 - MongoDB Input-User login data.0 - Finished processing (I=0, O=0, R=0, W=111473, U=0, E=0)
    2016-12-12 11:17:03,806 INFO [org.pentaho.di] 2016/12/12 11:17:03 - week strt date.0 - Finished processing (I=0, O=0, R=111473, W=111473, U=0, E=0)
    2016-12-12 11:17:03,812 INFO [org.pentaho.di] 2016/12/12 11:17:03 - week end date.0 - Finished processing (I=0, O=0, R=111473, W=111473, U=0, E=0)
    2016-12-12 11:17:03,818 INFO [org.pentaho.di] 2016/12/12 11:17:03 - change frmt.0 - Finished processing (I=0, O=0, R=111473, W=111473, U=0, E=0)
    2016-12-12 11:17:03,831 INFO [org.pentaho.di] 2016/12/12 11:17:03 - collect week data.0 - Finished processing (I=0, O=0, R=111473, W=111473, U=0, E=0)
    2016-12-12 11:17:03,835 INFO [org.pentaho.di] 2016/12/12 11:17:03 - trash.0 - Finished processing (I=0, O=0, R=111362, W=111362, U=0, E=0)
    2016-12-12 11:17:03,836 INFO [org.pentaho.di] 2016/12/12 11:17:03 - sort user.0 - Finished processing (I=0, O=0, R=111, W=111, U=0, E=0)
    2016-12-12 11:17:04,156 INFO [org.pentaho.di] 2016/12/12 11:17:04 - filter user type.0 - Finished processing (I=0, O=0, R=7071, W=92, U=0, E=0)
    2016-12-12 11:17:04,159 INFO [org.pentaho.di] 2016/12/12 11:17:04 - sort user & date.0 - Finished processing (I=0, O=0, R=92, W=92, U=0, E=0)
    2016-12-12 11:17:04,165 INFO [org.pentaho.di] 2016/12/12 11:17:04 - aggrgt total logins.0 - Finished processing (I=0, O=0, R=92, W=59, U=0, E=0)
    2016-12-12 11:17:04,170 INFO [org.pentaho.di] 2016/12/12 11:17:04 - grp user.0 - Finished processing (I=0, O=0, R=59, W=59, U=0, E=0)
    2016-12-12 11:17:04,618 INFO [org.pentaho.di] 2016/12/12 11:17:04 - Script.0 - Optimization level set to 9.
    2016-12-12 11:17:04,846 INFO [org.pentaho.di] 2016/12/12 11:17:04 - Script.0 - Finished processing (I=0, O=0, R=59, W=59, U=0, E=0)
    2016-12-12 11:17:04,851 INFO [org.pentaho.di] 2016/12/12 11:17:04 - sort status.0 - Finished processing (I=0, O=0, R=59, W=59, U=0, E=0)
    2016-12-12 11:17:04,855 INFO [org.pentaho.di] 2016/12/12 11:17:04 - group status.0 - Finished processing (I=0, O=0, R=59, W=1, U=0, E=0)
    2016-12-12 11:17:04,859 INFO [org.pentaho.di] 2016/12/12 11:17:04 - format data.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:04,864 INFO [org.pentaho.di] 2016/12/12 11:17:04 - get week dates.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:04,867 INFO [org.pentaho.di] 2016/12/12 11:17:04 - calculator.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:04,870 INFO [org.pentaho.di] 2016/12/12 11:17:04 - date format.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:04,874 INFO [org.pentaho.di] 2016/12/12 11:17:04 - concate period.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:04,877 INFO [org.pentaho.di] 2016/12/12 11:17:04 - select values.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:04,882 INFO [org.pentaho.di] 2016/12/12 11:17:04 - Result.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016-12-12 11:17:05,035 INFO [org.pentaho.di] 2016/12/12 11:17:05 - tra_mongo_weekly_test - All transaction of database connection 'USER_RPT_JNDI' where committed at the end of the transformation.
    2016-12-12 11:17:57,009 ERROR [org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor] 1703548144: Report processing failed.
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: Select usertype from user_type order by ord_id
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:214)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:205)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:69)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:508)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:362)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getValues(DefaultListParameter.java:110)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.computeValidListValue(DefaultReportParameterValidator.java:336)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validateSingleParameter(DefaultReportParameterValidator.java:274)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validate(DefaultReportParameterValidator.java:154)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForMasterReport(ProcessState.java:249)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:481)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processReport(AbstractReportProcessor.java:1713)
    at org.pentaho.reporting.platform.plugin.output.PageableHTMLOutput.generate(PageableHTMLOutput.java:178)
    at org.pentaho.reporting.platform.plugin.SimpleReportingComponent.execute(SimpleReportingComponent.java:948)
    at org.pentaho.reporting.platform.plugin.ExecuteReportContentHandler.createReportContent(ExecuteReportContentHandler.java:161)
    at org.pentaho.reporting.platform.plugin.ReportContentGenerator.createContent(ReportContentGenerator.java:96)
    at org.pentaho.platform.engine.services.solution.SimpleContentGenerator.createContent(SimpleContentGenerator.java:60)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutput.generateContent(GeneratorStreamingOutput.java:236)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutput.write(GeneratorStreamingOutput.java:163)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutputProvider.writeTo(GeneratorStreamingOutputProvider.java:54)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutputProvider.writeTo(GeneratorStreamingOutputProvider.java:33)
    at com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:306)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1479)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
    at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
    at org.pentaho.platform.web.servlet.JAXRSServlet.service(JAXRSServlet.java:108)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:233)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:376)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:209)
    ... 91 more
    Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)
    at java.net.SocketInputStream.read(SocketInputStream.java:122)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
    at org.postgresql.core.PGStream.receiveChar(PGStream.java:280)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1916)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    ... 101 more
    2016-12-12 11:17:57,037 ERROR [org.pentaho.reporting.platform.plugin.ReportContentGenerator] Failed at query: Select usertype from user_type order by ord_id
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: Select usertype from user_type order by ord_id
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:214)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:205)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:69)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:508)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:362)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultListParameter.getValues(DefaultListParameter.java:110)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.computeValidListValue(DefaultReportParameterValidator.java:336)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validateSingleParameter(DefaultReportParameterValidator.java:274)
    at org.pentaho.reporting.engine.classic.core.parameters.DefaultReportParameterValidator.validate(DefaultReportParameterValidator.java:154)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForMasterReport(ProcessState.java:249)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:481)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processReport(AbstractReportProcessor.java:1713)
    at org.pentaho.reporting.platform.plugin.output.PageableHTMLOutput.generate(PageableHTMLOutput.java:178)
    at org.pentaho.reporting.platform.plugin.SimpleReportingComponent.execute(SimpleReportingComponent.java:948)
    at org.pentaho.reporting.platform.plugin.ExecuteReportContentHandler.createReportContent(ExecuteReportContentHandler.java:161)
    at org.pentaho.reporting.platform.plugin.ReportContentGenerator.createContent(ReportContentGenerator.java:96)
    at org.pentaho.platform.engine.services.solution.SimpleContentGenerator.createContent(SimpleContentGenerator.java:60)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutput.generateContent(GeneratorStreamingOutput.java:236)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutput.write(GeneratorStreamingOutput.java:163)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutputProvider.writeTo(GeneratorStreamingOutputProvider.java:54)
    at org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutputProvider.writeTo(GeneratorStreamingOutputProvider.java:33)
    at com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:306)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1479)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
    at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
    at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:233)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:376)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:209)
    ... 91 more
    Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)
    at java.net.SocketInputStream.read(SocketInputStream.java:122)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
    at org.postgresql.core.PGStream.receiveChar(PGStream.java:280)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1916)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    ... 101 more
    2016-12-12 11:21:06,788 ERROR [org.pentaho.platform.web.http.api.resources.GeneratorStreamingOutput] Error generating content from content generator with id [parameter]
    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: Select usertype from user_type order by ord_id
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:214)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162)
    org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:376)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:209)
    ... 86 more
    Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)
    at java.net.SocketInputStream.read(SocketInputStream.java:122)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
    at org.postgresql.core.PGStream.receiveChar(PGStream.java:280)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1916)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
    ... 96 more

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Increase the socket timeout. Look at the postgresql documentation for details on what properties to set:

    https://jdbc.postgresql.org/document...3/connect.html
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.