0046512: Export SAF-T not working with huge amount of data

Description

The process to export SAF-T document fails with big amount of data.

Error is:

2021-05-02 23:29:25,086 [DefaultQuartzScheduler_Worker-5] ERROR com.practicsbs.saftpt.data.ExportSaftPT - could not extract ResultSet
org.hibernate.exception.JDBCConnectionException: could not extract ResultSet
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2173) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1936) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2695) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2678) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2512) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.Loader.list(Loader.java:2507) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1999) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        at org.openbravo.dal.service.OBCriteria.list(OBCriteria.java:103) ~[classes/:?]
        at com.practicsbs.saftpt.data.ExportMFCustomer.getDocumentsTaxIds(ExportMFCustomer.java:190) ~[classes/:?]
        at com.practicsbs.saftpt.data.ExportMFCustomer.createBP(ExportMFCustomer.java:46) ~[classes/:?]
        at com.practicsbs.saftpt.data.ExportMasterFiles.createMasterFiles(ExportMasterFiles.java:27) ~[classes/:?]
        at com.practicsbs.saftpt.data.ExportSaftPT.createSaftFile(ExportSaftPT.java:263) [classes/:?]
        at com.practicsbs.saftpt.data.ExportSaftPT.doExecute(ExportSaftPT.java:138) [classes/:?]
        at org.openbravo.service.db.DalBaseProcess.execute(DalBaseProcess.java:85) [classes/:?]
        at org.openbravo.scheduling.DefaultJob.execute(DefaultJob.java:65) [classes/:?]
        at org.quartz.core.JobRunShell.run(JobRunShell.java:202) [quartz-1.6.2.jar:1.6.2]
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:525) [quartz-1.6.2.jar:1.6.2]
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:332) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at jdk.internal.reflect.GeneratedMethodAccessor185.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc.jar:?]
        at com.sun.proxy.$Proxy75.executeQuery(Unknown Source) ~[?:?]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ~[hibernate-core-5.4.2.Final.jar:5.4.2.Final]
        ... 22 more
Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 268423
        at org.postgresql.core.PGStream.sendInteger2(PGStream.java:224) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1440) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1762) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]
        at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1326) ~[postgresql-42.2.2.jre7.jar:42.2.2.jre7]

Steps To Reproduce

ON an environment with big amount of data.
Execute the export for 1 month.

The error seems to be related to the number of elements on the list put on the IN clause

https://stackoverflow.com/questions/1009706/postgresql-max-number-of-parameters-in-in-clause [^]

Error on that line:

   if (invoiceTaxIds.size() > 0)
      bpsCrit.add(Restrictions.not(Restrictions.in(Order.PROPERTY_SAFTTAXID, invoiceTaxIds)));

    invoiceTaxIds.addAll(bpsCrit.list());

Notes
(0127859) psanjuan (manager) 2021-05-06 13:52	This issue can not be managed as a defect, as a full refactor of SAFT code needs to be accomplished. Workaround: 1 - to include only the sale invoices 2 - split the file as required if the volume of information requires to do so (Dependendo do volume de faturas emitidas, a extração e envio poderá ser fracionada em períodos mais curtos, por exemplo, por semana ou por dia.)

(0131258) aferraz (developer) 2021-08-23 10:02	Performance problems have been fixed in the SAFT owned by Openbravo, starting from 19Q3 version: https://gitlab.com/openbravo/product/pmods/com.practicsbs.saftpt/-/merge_requests/7 [^]

Issue History
Date Modified	Username	Field	Change
2021-05-05 13:17	egoitz	New Issue
2021-05-05 13:17	egoitz	Assigned To	=> psanjuan
2021-05-05 13:17	egoitz	Resolution time	=> 1620338400
2021-05-06 13:52	psanjuan	Note Added: 0127859
2021-05-06 13:52	psanjuan	Type	defect => design defect
2021-05-13 14:37	markmm82	Assigned To	psanjuan => markmm82
2021-05-13 22:05	markmm82	Status	new => scheduled
2021-08-23 10:02	aferraz	Note Added: 0131258
2021-08-23 10:02	aferraz	Status	scheduled => resolved
2021-08-23 10:02	aferraz	Resolution	open => fixed
2021-08-23 10:03	aferraz	Assigned To	markmm82 => vmromanos
2021-08-23 10:03	aferraz	Status	resolved => closed
2021-08-23 10:03	aferraz	Assigned To	vmromanos => markmm82

Relationships [ Relation Graph ] [ Dependency Graph ]