[Bug] Adapter 7.1.0 ignores self.sequence_name, includes ID in INSERT instead of using RETURNING ID (Rails 7.1)

[TheSynthMaster]

Hi,

I'm in the process of porting a Rails 5 app to Rails 7.1.0 (using Ruby 3.4.1). Due to Oracle database requirements, I'm using the activerecord-oracle_enhanced-adapter gem, version 7.1.0.

I've encountered a persistent issue regarding primary key generation using Oracle sequences, and despite extensive debugging (with AI assistance), I haven't been able to resolve it.

Environment:

• Rails: 7.1.0
• Ruby: 3.4.1
• Adapter: activerecord-oracle_enhanced-adapter 7.1.0
• Database: Oracle 19c
• Oracle Client: Oracle instant client 23.6.0.24.10

Scenario:

I have several tables, for example, CATEGORIES, with a primary key column named ID (Type NUMBER(10)). The primary key values are intended to be populated using a corresponding Oracle sequence, e.g., CATEGORIES_SEQ.

Crucially, there is NO database trigger (BEFORE INSERT) configured to populate the ID column from the sequence. The expectation is that the oracle-enhanced-adapter handles this automatically when self.sequence_name is set in the model.

Model Configuration (app/models/category.rb):

# Inheriting from ActiveRecord::Base in this specific model
# (Note: Tried inheriting from ApplicationRecord, but it made no difference)
class Category < ActiveRecord::Base
  # Explicitly set the sequence name
  self.sequence_name = 'CATEGORIES_SEQ'

  # Associations
  belongs_to :company
  belongs_to :service

  # Validations
  validates_presence_of :code
  validates_uniqueness_of :code, :case_sensitive => false, :scope => [:company_id, :service_id]

  # Simple callback (Note: Temporarily disabling this had no effect on the issue)
  # before_save {|category| category.code.upcase!}
end

Problem Description:

When attempting to create a new record (e.g., Category.create(attributes) or @category = Category.new(attributes); @category.save), the oracle-enhanced-adapter does not generate the expected SQL statement that omits the ID and uses the RETURNING clause.

Expected SQL Behavior:

INSERT INTO "CATEGORIES" ("CODE", "DESCRIPTION", "COMPANY_ID", "SERVICE_ID", "CREATED_AT", "UPDATED_AT", "LOCK_VERSION")
VALUES (:a1, :a2, :a3, :a4, :a5, :a6, :a7)
RETURNING "ID" INTO :returning_id

Actual SQL Behavior (Observed in Logs):

Instead, the adapter generates an SQL statement that includes the ID column in the INSERT statement, providing a seemingly arbitrary value (which is not coming from the sequence or the application code). This prevents the Oracle sequence from being used and causes the Rails model instance not to be populated with the correct, database-generated ID after saving.

-- Example from actual logs: Note the inclusion of "ID" and :a8

INSERT INTO "CATEGORIES" ("CODE", "DESCRIPTION", "CREATED_AT", "UPDATED_AT", "COMPANY_ID", "SERVICE_ID", "LOCK_VERSION", "ID")
VALUES (:a1, :a2, :a3, :a4, :a5, :a6, :a7, :a8)
-- Followed by bind parameters, where :a8 gets a value like 691, etc.
-- Example Bind Parameters: [["code", "T"], ..., ["lock_version", 0], ["id", 691]]

Consequently, after @category.save returns true, @category.id remains nil or has the incorrect value sent in the INSERT, causing subsequent operations (like redirects redirect_to @category) to fail.

Debugging Steps & Verifications Performed (None Resolved the Issue):

• Sequence Name: Confirmed the sequence CATEGORIES_SEQ exists in the Oracle database and the name in self.sequence_name = 'CATEGORIES_SEQ' matches exactly (case-sensitive).

• Primary Key: Confirmed the primary key column name is exactly ID in Oracle. The model does not explicitly set self.primary_key.

• Adapter Version: Confirmed using the latest adapter version compatible with Rails 7.1 (7.1.0).

• No Explicit ID Assignment: Thoroughly checked the application code (controllers new/create, model callbacks before_validation, before_create, after_initialize, concerns) and confirmed no code path assigns a value to category.id before saving a new record.

• Request Parameters: Inspected incoming params in the create action; they do not contain an id key/value for the category being created.

• Object State Before Save: Added debug logs immediately after Category.new(params) and confirmed that the object has id == nil and new_record? == true.

• Inheritance: Changed model inheritance from ActiveRecord::Base to ApplicationRecord; this had no effect.

• Callbacks: Temporarily removed a simple before_save callback; this had no effect.

• Adapter Downgrade (Attempted/Considered): We considered downgrading the adapter to a ~> 7.0.x version to test for regressions, but encountered cross compatibility issues which prevented this test..

• Database Trigger: Re-confirmed there is no BEFORE INSERT trigger on the CATEGORIES table responsible for setting the ID.

Conclusion:

Given that all standard configuration points and application-level causes seem to be ruled out, this strongly suggests a potential bug or unexpected behavior within the activerecord-oracle_enhanced-adapter version 7.1.0 (or its interaction with Rails 7.1) where the self.sequence_name directive is being ignored or misinterpreted, leading to incorrect SQL generation for new records.

Workaround:

The only way found to make saving work correctly without modifying the database structure (e.g., switching to Identity Columns, which caused other issues and is undesirable) was to implement a before_validation :set_id_from_sequence, on: :create callback in the Rails model to manually fetch the next sequence value from Oracle (SELECT CATEGORIES_SEQ.nextval FROM DUAL) and assign it to self.id. While functional, this is not ideal as it moves DB logic into the application and bypasses the adapter's intended mechanism.

Could you please look into this? Has anyone else reported similar behavior with sequences on Rails 7.1 / Adapter 7.1.0? Any insights or suggestions would be greatly appreciated.

Thanks!

[TheSynthMaster]

Further testing confirms the core issue: When self.sequence_name = 'CATEGORIES_SEQ' (and self.table_name = 'CATEGORIES') are set, the adapter appears to incorrectly pre-fetch the next value from the sequence (e.g., SELECT CATEGORIES_SEQ.nextval FROM DUAL) before building the INSERT statement.

It then includes the "ID" column in the INSERT statement and provides the pre-fetched sequence value as a bind parameter, as shown in this log:

INSERT INTO "CATEGORIES" ("CODE", ..., "LOCK_VERSION", "ID") VALUES (:a1, ..., :a7, :a8) [["code", "1111"], ..., ["lock_version", 0], ["id", 1000]]

This behavior is incorrect because:

It bypasses Oracle's standard mechanism for populating the ID via DEFAULT or trigger during the INSERT.

It prevents the use of the RETURNING "ID" clause.

Crucially, the adapter fails to update the in-memory ActiveRecord object's id attribute after the save, leaving @category.id as nil even though the record was successfully inserted into the database with the pre-fetched sequence ID (e.g., 1000).

This pre-fetching and explicit insertion of the ID seems to be the root cause of the problem when self.sequence_name is used.

[TheSynthMaster]

Problem Description (Revised):

The actual behavior observed with activerecord-oracle_enhanced-adapter 7.1.0 / Rails 7.1.0 when self.sequence_name is set is as follows:

The adapter ignores any ID value that might already be present on the ActiveRecord object before saving.
During the _create_record process, the adapter internally executes SELECT sequence.nextval FROM DUAL for the specified sequence.
It then constructs an INSERT statement that explicitly includes the "ID" column, using the value obtained from its internal nextval call.
The INSERT executes successfully, creating the record in the database with the ID from the adapter's internal nextval.
Crucially, the adapter then FAILS to update the in-memory ActiveRecord object's id attribute. The object remains with a nil (or its pre-save) ID, not reflecting the ID that was actually inserted into the database.

Evidence from Logs:
This sequence confirms the behavior:

# Log from a workaround patch *before* calling the original _create_record:
# Shows object ID might be nil or pre-assigned by a test patch
[OracleSequenceFixAggressive] Calling original _create_record for Category. Current ID on object: 1031. Prefetched ID was: 1031

# The actual INSERT executed by the original adapter code - Note it uses ID 1032!
  Category Create (9.6ms)  INSERT INTO "CATEGORIES" ("CODE", ..., "LOCK_VERSION", "ID") VALUES (:a1, ..., :a7, :a8)  [..., ["id", 1032]]

# Log immediately after the original adapter code returns - Object ID is still nil!
[OracleSequenceFixCurrval] Returned from original _create_record for Category. Current ID on object: nil. Result: nil

This mismatch (DB has ID 1032, Rails object has ID nil) causes subsequent operations like redirect_to @category or finding records based on the object's ID to fail (e.g., ActionController::UrlGenerationError ... missing required keys: [:id]).

Successful Workaround (Using currval):
Since the adapter does successfully insert the record using the correct sequence value, we can work around the issue by retrieving the actually inserted ID using sequence.currval after the original _create_record method finishes and manually assigning it to the object.

The following patch, applied via an initializer (config/initializers/oracle_sequence_fix.rb), implements this workaround globally. Models using sequences still need to define self.sequence_name or follow the TABLE_NAME_SEQ convention for this patch to work.

config/initializers/oracle_sequence_fix_v4_currval_fix.rb

module OracleSequenceWorkaroundPatchCurrvalFix
  def _create_record(*)
    determined_sequence_name = nil
    sequence_was_used = false # Flag to track if sequence logic should apply

    # --- Determine Sequence Name (Before Super) ---
    # Determines sequence name either from explicit self.sequence_name
    # or by convention (TABLE_NAME_SEQ), checking if derived sequence exists.
    # Sets sequence_was_used = true if a valid sequence is identified.
    if id.blank? && self.class.connection.adapter_name == 'OracleEnhanced'
      explicit_sequence_name = self.class.sequence_name
      if explicit_sequence_name.present?
        determined_sequence_name = explicit_sequence_name
        sequence_was_used = true
      else
        table_name = self.class.table_name
        if table_name.present?
          derived_sequence_name = "#{table_name.upcase}_SEQ"
          connection = self.class.connection
          quoted_sequence_name = connection.quote_table_name(derived_sequence_name)
          begin
            # Light check to see if derived sequence likely exists
            connection.select_value("SELECT 1 FROM #{quoted_sequence_name} WHERE ROWNUM = 1")
            determined_sequence_name = derived_sequence_name
            sequence_was_used = true
          rescue ActiveRecord::StatementInvalid => e
             # Handle ORA-02289 only if sequence was derived
             unless e.message.include?('ORA-02289') && explicit_sequence_name.blank?
                Rails.logger.error "[OracleSequenceFixCurrval] Error checking derived sequence '#{derived_sequence_name}': #{e.message}"
             end
          end
        end
      end
      Rails.logger.debug { "[OracleSequenceFixCurrval] Sequence check complete. Determined sequence: '#{determined_sequence_name || 'None'}' for #{self.class.name}. sequence_was_used: #{sequence_was_used}" }
    end

    # --- Call Original Method ---
    # Let the adapter perform its flawed INSERT (which calls nextval internally)
    Rails.logger.debug { "[OracleSequenceFixCurrval] Calling original _create_record for #{self.class.name}." }
    result = super
    current_id_after_super = self.id # Get ID immediately after super returns
    Rails.logger.debug { "[OracleSequenceFixCurrval] Returned from original _create_record for #{self.class.name}. Current ID on object: #{current_id_after_super.inspect}. Result: #{result.inspect}" }

    # --- Post-Super ID Correction (Using CURRVAL) ---
    # If a sequence was identified AND the object's ID wasn't populated by super
    if sequence_was_used && current_id_after_super.blank? && determined_sequence_name.present?
      begin
        Rails.logger.info "[OracleSequenceFixCurrval] Object ID is blank after save for #{self.class.name}. Attempting to fetch CURRVAL from sequence '#{determined_sequence_name}'."
        connection = self.class.connection
        quoted_sequence_name = connection.quote_table_name(determined_sequence_name)

        # Fetch the ID that was *actually* inserted using CURRVAL
        inserted_id = connection.select_value("SELECT #{quoted_sequence_name}.currval FROM DUAL")

        if inserted_id.nil?
           Rails.logger.error "[OracleSequenceFixCurrval] Failed to fetch CURRVAL for sequence '#{determined_sequence_name}'. select_value returned nil."
        else
          Rails.logger.info "[OracleSequenceFixCurrval] Fetched CURRVAL ID: #{inserted_id}. Assigning it to #{self.class.name}."
          pk = self.class.primary_key

          # Forcefully assign the correct ID to the object using standard method
          write_attribute(pk, inserted_id)

          # Clean up object state
          clear_attribute_changes([pk]) if respond_to?(:clear_attribute_changes)
          reset_attribute!(pk) if respond_to?(:reset_attribute!) # Check if method exists

          Rails.logger.info "[OracleSequenceFixCurrval] State after forcing ID from CURRVAL: self.id=#{self.id.inspect}"
        end

      rescue ActiveRecord::StatementInvalid => e
        # Handle potential CURRVAL errors (like ORA-08002)
        if e.message.include?('ORA-08002')
           Rails.logger.error "[OracleSequenceFixCurrval] Failed to fetch CURRVAL for sequence '#{determined_sequence_name}' (ORA-08002)."
        else
           Rails.logger.error "[OracleSequenceFixCurrval] Error fetching CURRVAL for sequence '#{determined_sequence_name}': #{e.message}"
        end
      rescue StandardError => e
        Rails.logger.error "[OracleSequenceFixCurrval] Unexpected error processing CURRVAL for '#{determined_sequence_name}': #{e.message}"
      end
    elsif sequence_was_used && !current_id_after_super.blank?
      Rails.logger.info "[OracleSequenceFixCurrval] Object ID (#{current_id_after_super}) was populated after save for #{self.class.name}. No CURRVAL fetch needed."
    end

    result # Return original result
  end
end

# Apply the patch
ActiveSupport.on_load(:active_record) do
  ActiveRecord::Persistence.prepend(OracleSequenceWorkaroundPatchCurrvalFix)
  Rails.logger.info "[OracleSequenceFixCurrvalFix] ActiveRecord::Persistence patched with CURRVAL-based Oracle sequence ID retrieval."
end

Summary of the Bug:

The bug lies in the adapter's failure to update the object's ID after performing the INSERT with an explicitly provided, internally sequence-derived value. A proper fix in the adapter should ideally involve omitting the ID from the INSERT and using the RETURNING ID clause, or at least correctly updating the object with the ID it chose to insert.
Hope this detailed analysis helps in addressing the issue. Thanks!