When you said "fetchSize set low", I assume you mean non-zero, a zero will fetch all the rows at once. How did you paginate your query with ExecuteSQLRecord? I was going to suggest GenerateTableFetch in front to paginate the queries for you, but it definitely seems like we should be able to do or configure something that will make PG happy.
Regards, Matt On Sat, Sep 2, 2023 at 11:36 AM Mark Payne <marka...@hotmail.com> wrote: > > Thanks for sharing the solution Mike. Is there something we need to update in > nifi to prevent this from biting others? > > Thanks > Mark > > Sent from my iPhone > > On Sep 2, 2023, at 9:48 AM, Joe Witt <joe.w...@gmail.com> wrote: > > > Nice. Gald you found it. > > On Sat, Sep 2, 2023 at 5:07 AM Mike Thomsen <mikerthom...@gmail.com> wrote: >> >> It was the PostgreSQL JDBC driver. If you don't paginate the query >> aggressively, it will try to load a significant chunk of the table into >> memory rather than just pulling chunks, even with fetchSize set low. >> >> On Fri, Sep 1, 2023 at 6:01 PM Mike Thomsen <mikerthom...@gmail.com> wrote: >>> >>> I have a three node cluster with an executesqlrecord processor with primary >>> execution only. The sql it runs is a straight forward select on a table >>> with about 44m records. If I leave it running, after about 10 min the node >>> becomes unresponsive and leaves the cluster. The query runs just fine in >>> jetbrains data grip on that postgresql server, so I don’t think it’s >>> anything weird with the db or query. Any ideas about what could be causing >>> this? Even with a high limit like 5m records the query doesn’t lock up the >>> NiFi node. >>> >>> Sent from my iPhone
