# Support for insert into ... returning

> Implement INSERT INTO RETURNING support in PO save to avoid the separate reload query when inserting records in PostgreSQL.

**Goal:** Technical  
**Developer:** [Heng Sin Low](https://github.com/hengsin)  
**Feature Ticket:** [IDEMPIERE-7204](https://idempiere.atlassian.net/browse/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)`:

```sql
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:

```java
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:

```java
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:

```java
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.

## Related tickets

- [IDEMPIERE-7204](https://idempiere.atlassian.net/browse/IDEMPIERE-7204)