Handling RETURNING Clause with UPSERT on Temporary Tables in SQLite: Missing Updated Rows

Observed Behavior: Missing RETURNING Rows During UPSERT on Temporary Tables

When executing an INSERT ... ON CONFLICT DO UPDATE (UPSERT) statement with a RETURNING clause on a temporary table in SQLite, the RETURNING clause fails to output rows for updates triggered by conflicts. This occurs even though the underlying data modification (e.g., incrementing a reference counter) succeeds. In contrast, the same operation on a regular (non-temporary) table returns all expected rows, including those updated via conflict resolution.

For example, consider a temporary table foo with a unique constraint on fooval:

CREATE TEMP TABLE foo (
  fooid INTEGER PRIMARY KEY,
  fooval INTEGER NOT NULL UNIQUE,
  refcnt INTEGER NOT NULL DEFAULT 1
);

Inserting duplicate values for fooval with an UPSERT:

INSERT INTO foo (fooval) VALUES (17), (4711), (17)
  ON CONFLICT (fooval) DO UPDATE SET refcnt = refcnt + 1
  RETURNING fooid;

The RETURNING clause outputs only two rows (1 and 2), omitting the third row (which triggers the DO UPDATE action). The final table state confirms the update occurred (refcnt for fooval=17 is 2), but the RETURNING clause does not reflect this. When the table is non-temporary, the RETURNING clause correctly outputs three rows, including the updated fooid=1.

This discrepancy highlights an inconsistency in SQLite’s handling of temporary tables during UPSERT operations with RETURNING clauses.

Root Cause: Internal Cursor Management for Temporary Tables During UPSERT

The issue stems from SQLite’s internal management of cursors (data structure pointers to database records) when resolving conflicts in UPSERT operations on temporary tables. Temporary tables in SQLite are stored in separate database objects (e.g., in-memory or attached transient databases) and follow different transaction and journaling rules compared to regular tables.

1. Cursor Reuse and Row Identification

During an INSERT operation, SQLite opens a cursor to write the new row. If a conflict occurs, the DO UPDATE action modifies the existing row. For regular tables, the cursor handling ensures that the updated row is tracked and included in the RETURNING output. However, for temporary tables:

• The cursor opened for the initial INSERT may be reused or closed prematurely after the conflict is detected.
• The DO UPDATE action modifies the existing row, but the cursor associated with the initial INSERT does not correctly reacquire the updated row for the RETURNING clause.

2. Transaction Journaling Differences

Temporary tables often use in-memory journals (MEMORY journal mode) instead of the default DELETE or WAL (Write-Ahead Logging) modes. This affects how row changes are tracked during conflict resolution. The journal may not log the updated row in a way that the RETURNING clause can access it, especially when the same statement performs both insertion and modification.

3. Optimization Flags for Temporary Tables

SQLite applies optimizations to temporary tables to reduce overhead, such as bypassing certain integrity checks or logging steps. These optimizations can interfere with the RETURNING clause’s ability to capture intermediate states of rows during UPSERT operations.

Resolution: Updating SQLite and Adjusting Temporary Table Usage

1. Apply the Official Fix

The issue was resolved in SQLite commit a2449bcc2c71d0f4. To resolve the problem:

• Update SQLite to a version containing this commit (post-3.40.0, released after November 2022).
• Verify the fix by re-running the UPSERT query on a temporary table. The RETURNING clause should now output all rows, including those updated via DO UPDATE.

2. Workarounds for Older SQLite Versions

If updating SQLite is not feasible, use these alternatives:

Option 1: Use Regular Tables Instead of Temporary Tables

If the temporary table does not require session-specific isolation, create it as a regular table. This avoids the cursor management issue entirely.

Option 2: Split INSERT and UPDATE Operations

Manually check for conflicts and perform updates in separate steps:

-- Insert new rows, ignoring conflicts
INSERT OR IGNORE INTO foo (fooval) VALUES (17), (4711), (17);

-- Update conflicting rows
UPDATE foo
SET refcnt = refcnt + 1
WHERE fooval IN (17, 4711, 17)
  AND changes() = 0;  -- Only update if the INSERT did not add a new row

-- Retrieve the modified rows
SELECT fooid FROM foo WHERE fooval IN (17, 4711, 17);

This bypasses UPSERT but achieves the same result with explicit control over conflict handling.

Option 3: Use a UNION ALL Query to Force Row Emission

Force SQLite to emit a row for the DO UPDATE action by using a synthetic query:

INSERT INTO temp.foo (fooval)
  SELECT 17 UNION ALL
  SELECT 4711 UNION ALL
  SELECT 17
  ON CONFLICT (fooval) DO UPDATE SET refcnt = refcnt + 1
  RETURNING CASE WHEN (SELECT 1 FROM temp.foo WHERE fooval = excluded.fooval) 
                 THEN fooid 
                 ELSE NULL 
            END AS fooid;

This workaround ensures the RETURNING clause processes each row individually.

3. Adjust Journal Modes for Temporary Tables

Force temporary tables to use the same journaling mode as regular tables:

PRAGMA temp_store_journal = PERSIST;  -- Default is MEMORY

This ensures that updates to temporary tables are logged in a way that the RETURNING clause can access them.

4. Validate Cursor Handling in Application Code

If using an ORM or database wrapper, ensure it does not interfere with cursor management for temporary tables. For example, some libraries may reset cursors after conflicts, leading to incomplete RETURNING results.

By addressing the internal cursor management differences between temporary and regular tables, developers can ensure consistent behavior across UPSERT operations. Always validate SQLite versions and consider temporary table optimizations when designing schemas requiring conflict resolution and row emission.