Audit AbstractAdapter `supports_*` capability flags for Oracle accuracy

[yahonda]

Background

Active Record's AbstractAdapter declares many supports_*? capability flags that adapters override to advertise feature support. Recently merged PRs (#2715, #2717, #2724, #2727; the in-flight #2726) showed a recurring pattern: the underlying Oracle feature was already implemented in oracle-enhanced for years, but the capability flag stayed at the AbstractAdapter default (false). The flag mismatch caused Rails core to take alternative paths or AR-level tests to skip Oracle paths needlessly.

For #2727 (supports_expression_index?), the entire library change was three lines:

def supports_expression_index?
  true
end

The dumper, the visitor, and the structure_dump path were all already wired correctly. The flag had simply not been updated to match.

Proposal

Do a systematic audit of every supports_*? method on ActiveRecord::ConnectionAdapters::AbstractAdapter (and at least the PG/MySQL overrides for comparison), and for each:

1. Classify Oracle's actual support: supported / unsupported / partial.
2. Compare against current oracle-enhanced override state.
3. File targeted PRs for any flag where Oracle does support the feature but oracle-enhanced reports false (or vice-versa).

Known state (post-recent PRs)

Already overridden to true:

• supports_advisory_locks? (existing)
• supports_check_constraints? — Wire up CHECK constraint DSL: add_check_constraint, t.check_constraint, dump round-trip #2717
• supports_common_table_expressions? (existing)
• supports_deferrable_constraints? (existing)
• supports_expression_index? — Override supports_expression_index? to true #2727
• supports_index_sort_order? — Override supports_index_sort_order? to true #2726 (in-flight)
• supports_insert_returning? — Align supports_insert_returning? with the adapter's actual RETURNING usage #2715
• supports_optimizer_hints? (existing)
• supports_unique_constraints? (existing, Add unique constraint DSL matching PostgreSQL adapter #2701)
• supports_validate_constraints? — Wire up validate_constraint / validate_check_constraint with NOVALIDATE support #2724
• supports_views? (existing)
• supports_virtual_columns? (existing)

Intentionally false:

• supports_partial_index? — Oracle has no true partial index; function-based workaround is a different concept (Investigate supports_partial_index? for Oracle #2712 closed).

Candidate flags to audit

Some that are likely already supported but possibly not flipped:

• supports_bulk_alter? — Oracle supports multiple operations in one ALTER TABLE
• supports_comments? / supports_comments_in_create? — COMMENT ON TABLE / COMMENT ON COLUMN work
• supports_datetime_with_precision? — TIMESTAMP(n) supported
• supports_disable_referential_integrity? — ALTER TABLE DISABLE CONSTRAINT exists
• supports_explain? — Oracle has EXPLAIN PLAN
• supports_foreign_keys? — obviously
• supports_indexes_in_create? — Oracle's CREATE TABLE accepts inline UNIQUE constraints (and the constraint-creates-index pattern); needs a closer look
• supports_insert_conflict_target? / supports_insert_on_duplicate_skip? / supports_insert_on_duplicate_update? — Oracle's MERGE and INSERT ... ON CONFLICT-equivalents
• supports_json? — Oracle has JSON column type (12c+)
• supports_lazy_transactions? — needs verification
• supports_materialized_views? — CREATE MATERIALIZED VIEW exists
• supports_savepoints? — SAVEPOINT works
• supports_set_constraints? — SET CONSTRAINTS DEFERRED works
• supports_transaction_isolation? — SET TRANSACTION ISOLATION LEVEL exists
• supports_transactional_indexes? — needs verification
• supports_table_comments? — see comments above

Likely PG-only (should stay false on Oracle):

• supports_extensions? — PG-specific
• supports_foreign_tables? — PG-specific (FDW)
• supports_index_include? — PG-specific (INCLUDE (col) syntax)
• supports_nulls_not_distinct? — PG 15+ specific
• supports_partitioned_tables? — Oracle has partitioning but the AR flag may target PG semantics specifically
• supports_string_aggregation? — needs check; Oracle has LISTAGG

Approach

1. For each flag, write a small probe: what does AR core do behind the flag? Is the Oracle equivalent already wired up in oracle-enhanced?
2. Group findings into:
   • "Flip the flag, no other changes" (like Override supports_expression_index? to true #2727)
   • "Flip the flag + small plumbing" (like Override supports_index_sort_order? to true #2726, Wire up validate_constraint / validate_check_constraint with NOVALIDATE support #2724)
   • "Larger work needed" — separate tracking issue
   • "Genuinely unsupported" — leave as false and consider documenting why
3. Open small PRs per flag (one PR per flag is the established pattern from Override supports_index_sort_order? to true #2726 / Override supports_expression_index? to true #2727).

Why now

After a survey of recent PRs there's a clear pattern of "the work was done years ago, the flag never caught up". A focused audit pass closes that gap and lets multi-DB Rails apps that target Oracle as a secondary backend get fewer if connection.adapter_name == 'OracleEnhanced' escape hatches.

Surfaced during the survey for #2702 / #2710; the audit is a sibling to issues #2711 (closed by #2726/#2727), #2712 (closed: stays false), #2713 (stored generated columns), #2714 (closed: prepared cache).

[yahonda]

Audit results: capability flag accuracy on the live connection

Ran conn.public_methods.grep(/^supports_/).sort against an Oracle Database 23ai Free server (23.26.1) and cross-referenced each flag against (a) the AbstractAdapter default, (b) the PostgreSQL / MySQL adapter override state, and (c) Oracle's actual feature support per the SQL Reference.

A. Already correct (true) — no action needed

supports_check_constraints?, supports_comments?, supports_common_table_expressions?, supports_concurrent_connections?, supports_datetime_with_precision?, supports_deferrable_constraints?, supports_drop_if_exists? (23.4+), supports_explain?, supports_expression_index?, supports_fetch_first_n_rows_and_offset? (12+), supports_foreign_keys?, supports_identity_columns? (12+), supports_index_sort_order?, supports_insert_returning?, supports_longer_identifier? (12.2+), supports_optimizer_hints?, supports_savepoints?, supports_transaction_isolation?, supports_unique_constraints?, supports_validate_constraints?, supports_views?, supports_virtual_columns? (11+).

B. Currently false, but Oracle is capable — flip candidates

Tier 1 — small, high return

Flag	Oracle support	AR core uses it for	Suggested change
supports_bulk_alter?	ALTER TABLE t ADD c1 ..., ADD c2 ..., MODIFY c3 ... is valid since 8i	change_table(t, bulk: true) collapses several DDL operations into one ALTER TABLE	Override to true and rely on AR core's default bulk_change_table (uses *_for_alter helpers); verify the existing helpers are wired or add the missing ones.
supports_disabling_indexes?	ALTER INDEX i INVISIBLE / VISIBLE since 11g	Schema dumper emits enabled: and AR exposes disable_index / enable_index on adapters where this returns true	Override to database_version >= "11", implement disable_index / enable_index on top of ALTER INDEX ... INVISIBLE / VISIBLE, and confirm dumper round-trip.

Tier 2 — need a small investigation before flipping

Flag	Question
supports_lazy_transactions?	AR core uses this in Transaction#materialize to delay BEGIN until the first DML. Need to confirm Oracle's autocommit / transaction semantics line up with the lazy materialization contract that AR expects (and that the JDBC and OCI paths agree).
supports_materialized_views?	Oracle has CREATE MATERIALIZED VIEW since 8i, but the flag's effect in AR core is small and mostly funnels into adapter-defined helpers. Confirm that flipping the flag without adding materialized_views / create_materialized_view etc. actually produces a usable result before opening the PR.
supports_partitioned_indexes?	Oracle has LOCAL and GLOBAL partitioned indexes since 8i. Need to confirm that the existing indexes(table) reader recognises partitioned indexes and that the dumper round-trips them; otherwise the flag flip is misleading.
supports_restart_db_transaction?	AR's Transaction#restart calls connection.restart_db_transaction when this is true. Need to confirm whether Oracle should implement that as ROLLBACK; <statement that re-enters the transaction> or whether the existing rollback_db_transaction; materialize! fallback is the correct shape.

Tier 3 — larger work, tracked elsewhere

Flag	Status
supports_insert_on_duplicate_skip?	Implementable via MERGE WHEN NOT MATCHED THEN INSERT. Tracked in #2733 alongside the other insert_all follow-ups.
supports_insert_on_duplicate_update?	Implementable via MERGE WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT (with the well-known ORA-38104 caveat that ON-clause columns cannot be in the UPDATE SET list). Tracked in #2733.
supports_insert_conflict_target?	PostgreSQL-shaped ON CONFLICT (target). Oracle MERGE is the closest equivalent but the API surface differs; bundle with the upsert work in #2733.

C. Intentionally false — leave with rationale

Flag	Reason to keep false
supports_advisory_locks?	DBMS_LOCK exists, but matching pg_advisory_lock semantics (session-scoped, transaction-scoped, with key types) needs a non-trivial wrapper and there is no clear demand.
supports_comments_in_create?	Oracle issues COMMENT ON TABLE / COLUMN as separate statements after CREATE TABLE; there is no inline-comment syntax.
supports_ddl_transactions?	DDL auto-commits in Oracle; cannot be rolled back.
supports_exclusion_constraints?	PostgreSQL-only feature.
supports_extensions?	PostgreSQL-only concept.
supports_foreign_tables?	PostgreSQL FDW concept; Oracle external tables are a different feature.
supports_index_include?	PostgreSQL-only INCLUDE (col) syntax.
supports_indexes_in_create?	Oracle CREATE TABLE accepts inline UNIQUE constraints (which create unique indexes), but there is no syntax for inlining a non-unique index. AR core uses this flag to decide whether to emit secondary indexes inside CREATE TABLE — false is the right answer for Oracle.
supports_json?	Oracle 12.1+ supports IS JSON constraints and 21c+ adds a native JSON type, but the existing false is intentional because the AR JSON type contract has a number of gaps against Oracle's storage shape. The override carries a long comment with the manual workaround.
supports_nulls_not_distinct?	PostgreSQL 15+ specific.
supports_partial_index?	Oracle has no true partial index. The function-based workaround is a different concept; intentionally false (#2712).

Sibling capabilities worth a follow-up read

Not supports_*? themselves but adjacent:

• prefetch_primary_key? — already overridden in oracle-enhanced (line 750).
• savepoint_errors_invalidate_transactions? — abstract default false; worth confirming against Oracle's savepoint behaviour.
• return_value_after_insert?(column) — abstract returns column.auto_populated?; behaves correctly under Align supports_insert_returning? with the adapter's actual RETURNING usage #2715 but worth a quick sanity check now that supports_insert_returning? is true.

Suggested PR sequencing

1. Tier 1 — open one PR per flag (matching the established Override supports_index_sort_order? to true #2726 / Override supports_expression_index? to true #2727 / Align supports_insert_returning? with the adapter's actual RETURNING usage #2715 pattern). Each PR should pair the flag flip with the minimal plumbing AR core expects on the true branch (e.g., disable_index / enable_index).
2. Tier 2 — open small investigation issues per flag rather than PRs, so the questions above are answered in writing before the flag changes.
3. Tier 3 — keep tracked under Wire up insert_all / upsert_all via Oracle MERGE for on_duplicate skip/update #2733 alongside the rest of the bulk-insert / MERGE work; do not split the capability flips out from the underlying implementation work.