DB Query Reader and INSERT RETURNING

[a.formella]

Hello!
PostgreSQL supports INSERT INTO… RETURNING * statements but DB Query Reader doesn’t.

The preview show an error but execution works and ID (alternate_loading_id) is correct but something is wrong with transaction because the new row is not visible in the table (transaction was rollbacked?)

Is it possible to insert this row and retrieve an ID in one query?

[NDekay]

Hello,

Thank you for contacting KNIME regarding this issue.

My understanding of the issue is that the DB Query Reader node is failing to properly parse valid postgresql INSERT INTO…RETURNING statements.

I have verified this behavior in AP 4.2.1; however, please confirm what version(s) of KNIME Analytics Platform (AP) you have experienced this issue on just for comparison.

At this time, I have created a defect/query designated AP-15183 for tracking; if there are any updates on this issue, I will communicate them to you further on this forum thread.

Regards,
Nickolaus

[a.formella]

Hello! Thank you for your reply.

My KNIME version is 4.1.4 on Windows 10. PostgreSQL 11.2.

[tobias.koetter]

Hello,
executing the statement using the DB Query Reader works. However pressing the Evaluate button in the node dialog won’t work since KNIME surrounds your statement with a limit statement as shown in the left part of the preview window. This is done to prevent fetching of all values just for evaluation but unfortunately fails for these kind of special statements that can not be wrapped within a limit statement. So executing the node should work.
Bye
Tobias

[a.formella]

tobias.koetter, the statement works, but the row is rollbacked.

[tobias.koetter]

Hello,
sorry, I have forgotten that the changes will not be committed automatically. To solve this problem you can surround the DB Query Reader node with the DB Transaction Start and DB Transaction End node. The DB Transaction End node will commit all changes. You can find an example workflow in the KNIME Hub.

Returning data for modified rows in PostgreSQL – tobias.koetter

Bye
Tobias

[a.formella]

Hello!
Your solution works but now I have other problem.
I can’t merge results using Cross Joiner from two transactions.

DB Query Readers are in try-catch blocks.

0:528:0:621 Unable to merge flow object stacks: Conflicting FlowObjects: Try-Catch Context vs. Try-Catch Context (loops/scopes not properly nested?)

I’m dreaming about autocommit in DB Query Reader

[Iris]

You need to add flow variable connection between the transaction ends and the cross joiners, connect both to a Merge Variables and than the Merge Variables to the Cross Joiner