Skip to main content
View as Markdown

Support for insert into ... returning

Goal: Technical
Developer: Heng Sin Low
Feature Ticket: IDEMPIERE-7204
Version: iDempiere 14

The problem​

When saving a new persistent object (PO) in iDempiere, PO.save() generated an INSERT INTO statement and executed it with DB.executeUpdate(...).

To initialize default values, sequence IDs, database defaults, and audit timestamps, the persistence layer immediately executed a separate SELECT query via PO.load(m_trxName):

SELECT * FROM TableName WHERE TableName_ID = ?

When centralized IDs were not used and the primary key was generated by the database sequence, an extra query was also executed to find the generated record ID by its UUID before reloading.

In high-concurrency environments or high-volume data imports (such as REST API batch operations and automated document processing), this separate SELECT round-trip for every inserted record increased database round-trips, network latency, and transaction duration.

What changed​

iDempiere now supports the INSERT INTO ... RETURNING * SQL clause for database dialects that provide it, such as PostgreSQL.

When inserting a new PO record without explicitly specified ID values:

  1. The persistence engine appends RETURNING * to the insert SQL statement.
  2. The statement executes through DB.executeInsertReturning(...), returning a ResultSet with the newly created row.
  3. The PO instance populates its internal fields directly from that ResultSet using load(rs).
  4. For tables without LOB columns, the subsequent SELECT query (load(m_trxName)) is completely bypassed.
  5. If the record ID is already populated by the returned row, the additional UUID lookup query is also omitted.

If the active database dialect does not support RETURNING * (such as Oracle), PO automatically falls back to the previous DB.executeUpdate(...) and load(m_trxName) sequence.

This ticket also includes related concurrency and performance improvements:

  • Table caching: Added a table name cache (s_cacheByName) in MTable and removed class-level synchronization locks from MTable.get(...).
  • Index lookup caching: Added MTableIndex.get(Properties, int, String) to cache unique constraint index lookups.
  • Recent item concurrency: Made m_label in MRecentItem volatile and removed synchronized locks from clearLabel.
  • Workflow caching: Cached document value workflows in MWorkflow.getDocValue using an unmodifiable map without synchronization blocks.
  • Changelog configuration lookup: Moved the SYSTEM_INSERT_CHANGELOG system configuration lookup outside the column processing loop in PO.saveNew().

How to set it up​

No manual setup or database migration is required.

The feature activates automatically when iDempiere connects to a PostgreSQL database. On databases without RETURNING * support, the previous save and reload mechanism continues to operate without changes.

If a specific custom table or model cannot use RETURNING * (for example, a table with custom triggers, rules, or views that restrict RETURNING), developers can disable it for that model by overriding is_UseInsertReturning().

Technical notes​

The core persistence and database abstraction layers introduce the following methods:

Database dialect capability​

The org.compiere.db.AdempiereDatabase interface adds:

public default boolean isInsertReturningSupported() {
    return false;
}

DB_PostgreSQL overrides this method to return true. DB_Oracle returns false.

Database helper methods​

The org.compiere.util.DB class adds:

public static boolean isInsertReturningSupported();

public static boolean executeInsertReturning(
    String sql,
    Object[] params,
    String trxName,
    Function<ResultSet, Boolean> loader
);

public static boolean executeInsertReturning(
    String sql,
    Object[] params,
    String trxName,
    Function<ResultSet, Boolean> loader,
    int timeOut
);

The method executes the insert statement as a query using CPreparedStatement.executeQuery(), advances to the first returned row (rs.next()), and passes the result set to the provided loader function.

Model extension point​

The org.compiere.model.PO class adds:

protected boolean is_UseInsertReturning() {
    return DB.isInsertReturningSupported();
}

Custom model classes can override is_UseInsertReturning() to return false if RETURNING * must be disabled for that specific table.

Migration impact​

None. Existing models and plug-ins remain backward compatible. Databases that do not support INSERT INTO ... RETURNING * continue to use the standard insert and reload sequence.