Klustron insert/update/delete...returning Statement

In some business scenarios, users need to insert, update, or delete one or more rows of target data, and then obtain the values of the rows or some of their fields, including the value of any legal expression formed by partial fields, after the insertion, update, or deletion.

For MySQL users, to achieve this, users usually need to execute the update statement in an explicit transaction, and then retrieve the update statement using the same query condition.

The reason for doing this in an explicit transaction is to prevent other concurrent transactions from updating the rows just updated by this connection. Otherwise, the rows obtained by the subsequent select statement will not be the rows just updated by this connection because these rows have been updated by other transactions.

In Oracle and PostgreSQL databases, however, users can use the returning clause of the update/delete statement to accomplish the above work with one statement.

The advantage of this is that it can improve efficiency and save costs. It eliminates the need for sending a separate select statement and performing a search for the target rows. This approach also avoids the overhead of managing an explicit transaction, such as sending begin and commit statements.

Therefore, using the update...returning statement is expected to improve performance by more than 10% compared to updating the rows and then selecting them within an explicit transaction.

This is a great feature. In fact, if you search for "mysql update returning", you can also find many articles on implementing similar oracle/postgresql single-statement update/delete queries with the returning clause in MySQL, using triggers, temporary tables, and other complex techniques.

However, these complex steps significantly reduce the efficiency of statement execution and are not easy to use (as triggers need to be created for each table).

To improve program efficiency, reduce development difficulties for users, and enhance the user experience of using Klustron, we have implemented the insert/update/delete... returning statement in Klustron-storage for use by the computing nodes. Based on the implementation of the PostgreSQL insert/update/delete...returning statement, we have added support for remote data read and write in the computing node.

As a result, Klustron now has the functionality of insert...returning, update...returning, and delete...returning statements, which respectively return the inserted rows of the insert statement, the updated rows of the update statement, and the deleted rows of the delete statement, including the values of any legal expression composed of the fields of these rows. Users can now use these features on Klustron.

The following figure shows this cool feature.

First, create a table and fill it with data: 

Then, we execute an update...returning statement: 

Next, we execute a delete...returning statement: 

Finally, explain the update/delete...returning statement: 

END