CockroachDB: $identitySelect may fail with unique_rowid()

[bpamiri]

Summary

CockroachDBModel inherits PostgreSQL's $identitySelect() which calls pg_get_serial_sequence() and currval(). These may not work correctly with CockroachDB's unique_rowid() default values.

Details

• PostgreSQL uses sequences behind SERIAL — currval() retrieves last inserted value
• CockroachDB's unique_rowid() doesn't use sequences, so pg_get_serial_sequence() returns NULL
• Need CockroachDB-specific identity retrieval, likely using RETURNING clause
• CockroachDB supports INSERT INTO ... RETURNING id natively

Files

• vendor/wheels/databaseAdapters/CockroachDB/CockroachDBModel.cfc — needs $identitySelect() override
• vendor/wheels/databaseAdapters/PostgreSQL/PostgreSQLModel.cfc:124-171 — inherited method

[bpamiri]

Analysis

Synopsis

CockroachDBModel inherits PostgreSQL's $identitySelect() which calls pg_get_serial_sequence() and currval() to retrieve the last inserted primary key. These functions don't work with CockroachDB's unique_rowid() default values since no sequence exists.

Merit Assessment

Recommendation: Proceed

• This is a confirmed blocker — after an INSERT, pg_get_serial_sequence() returns NULL for unique_rowid() columns, causing the ORM's create() and save() to fail or return no key
• The inherited method at PostgreSQLModel.cfc:124-171 is tightly coupled to PostgreSQL's sequence-based identity model
• CockroachDB natively supports INSERT ... RETURNING id, which is the correct approach
• This affects every model save()/create() call — the most fundamental ORM operation

Implementation Plan

1. Override $identitySelect() in vendor/wheels/databaseAdapters/CockroachDB/CockroachDBModel.cfc

2. The override should use RETURNING <primaryKey> instead of currval(pg_get_serial_sequence(...)). Two approaches:

   Option A (preferred): Modify the INSERT SQL to include RETURNING clause. This requires intercepting the SQL before execution, which may mean overriding $querySetup() instead. Examine how the SQL array is built in Base.cfc:$performQuery() / $executeQuery().

   Option B: Post-INSERT query. After the INSERT, run SELECT lastval() — CockroachDB does support lastval() in some contexts, but it is unreliable with unique_rowid().

3. The cleanest approach is likely to modify the SQL array in $querySetup() to append RETURNING <primaryKey> to INSERT statements, then read the result from the query output rather than needing $identitySelect() at all.

4. Key files:

   • vendor/wheels/databaseAdapters/CockroachDB/CockroachDBModel.cfc — add $identitySelect() override
   • vendor/wheels/databaseAdapters/Base.cfc:107-118 — where $identitySelect() result is consumed in $executeQuery()
   • vendor/wheels/databaseAdapters/PostgreSQL/PostgreSQLModel.cfc:124-171 — inherited method to study

5. Estimated complexity: Medium — requires understanding the SQL execution pipeline and may need changes to how CockroachDB INSERT results are processed

Dependencies & Risks

• Prerequisite: CockroachDB: addPrimaryKeyOptions uses SERIAL (unsupported) #1970 (PK options must generate valid DDL before identity retrieval matters)
• Related: CockroachDB: add to CI test matrix and Docker Compose #1973 (CI testing essential — this is impossible to validate without a running CockroachDB instance)
• Breaking change risk: None — new adapter, no existing users
• Risk: The RETURNING clause approach may require touching the query execution pipeline in Base.cfc:$executeQuery(), which is shared across all adapters. Careful scoping needed to avoid regressions on other databases.

---

Automated analysis by @claude — review and adjust as needed.