I have a database lookup inside a DataGenerator as the first component in a graph. Subsequent components/phases in the graph are time consuming. Unfortunately, the original database lookup keeps its transaction open for the duration of the graph (in Postgres this is observed as an “idle in transaction” state in pg_stat_activity) ands ends up blocking some other operations).
I tried putting the initial DataGenerator in separate phase, but that had no effect. My connection is set as “thread safe” in the advanced tab.
A simple/nonsensical graph that exhibits this issue is attached; it includes a DataGenerator with DB lookup, a simple reformat that sleeps for 5 seconds each row, and a trash output.
Any suggestions?
Hi nierman,
Connection in DB lookup table is initialized during the graph initialization and it lasts until the graph is finished. It is not related to DataGenerator despite the fact that there is no other component using the lookup. And phases unfortunately do not help you in this case.
I admit this should be improved, I have raised an issue in our bug tracker, see https://bug.javlin.eu/browse/CLO-4042
Until the improvement is done, you can use a workaround with jobflows (or RunGraphs if you do not have an access to jobflows). Please create a main jobflow containing two ExecuteGraph components. The first one (phase 0) loads data from DB and saves it into a temp file using CloverDataWriter. The second one (phase 1) reads the temp file and contains the components from phase 1 of your example graph. Using this solution, the connection for db lookup is released when the first child graph is finished.
I hope this solution meets your requirement.
Regards,
ah, ok, that’s too bad. It would be ok if it were just keeping a connection open for the duration of the graph, but it’s starting a transaction and not finishing that for the duration (i.e., with either a commit or rollback); I guess you are using a cursor to fetch results in stages for potentially large lookup result sets. In addition to the phase ordering I also tried retrieving all of the lookup’s results in that first component using next() repeatedly in a loop, but the transaction remained in its “idle in transaction” state in that case as well.
I’ll probably pursue a temporary fix with either the temp files or moving the lookup tables to another database.