Missing relation indexes on historical tables

[tagliala]

When using t.references or t.belongs_to in a migration, Chronomodel does not create the reference index in the historical table

Reproducible test case

# frozen_string_literal: true

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'

  gem 'chrono_model'
  # Test against latest Chronomodel:
  # gem 'chrono_model', github: 'ifad/chronomodel'

  gem 'pg'
  gem 'debug'
  gem 'rails'
end

require 'chrono_model'
require 'minitest/autorun'
require 'logger'
require 'debug'

# Needs a database called `chronomodel_test`

ActiveRecord::Base.establish_connection(adapter: 'chronomodel', database: 'chronomodel_test')
ActiveRecord::Base.logger = Logger.new($stdout)

ActiveRecord::Schema.define do
  enable_extension :btree_gist

  create_table :activities, temporal: true, force: true do |t|
    t.string :name
    t.timestamps
  end

  create_table :activity_roadmaps, temporal: true, force: true do |t|
    t.belongs_to :activity
    t.string :name

    t.timestamps
  end
end

class Activity < ActiveRecord::Base
  include ChronoModel::TimeMachine

  has_one :activity_roadmap, dependent: :destroy
end

class ActivityRoadmap < ActiveRecord::Base
  include ChronoModel::TimeMachine

  belongs_to :activity
end

SQL = <<~SQL.squish
  SELECT 1 AS one
    FROM pg_indexes
    WHERE tablename = 'activity_roadmaps'
    AND schemaname = '%<schemaname>s'
    AND indexname = 'index_activity_roadmaps_on_activity_id';
SQL

class BugTest < Minitest::Test
  def test_temporal_index
    refute_empty ActiveRecord::Base.connection.select_values(format(SQL, schemaname: 'temporal'))
  end

  def test_history_index
    refute_empty ActiveRecord::Base.connection.select_values(format(SQL, schemaname: 'history'))
  end
end

[tagliala]

When trying to understand what was going on with chronomodel, I lost myself inside AR internals, where they use ReferenceDefinition#.add in case of add_reference and ReferenceDefinition#.add_to in case of t.reference

add_reference works, t.references partially works, the index is being ignored but the column is being created, which is unexpected. Ref: failing spec at #319

[tagliala]

Workaround

# frozen_string_literal: true

namespace :chronomodel do
  desc "Sync indexes from temporal to history schema"
  task sync_history_indexes: :environment do
    def get_tables(schema)
      ActiveRecord::Base.connection.execute(<<-SQL.squish).values.flatten
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = '#{schema}'
        ORDER BY table_name
      SQL
    end

    def get_non_unique_indexes(schema, table)
      ActiveRecord::Base.connection.execute(<<-SQL.squish).values
        SELECT
          i.relname AS index_name,
          pg_get_indexdef(i.oid) AS index_definition
        FROM
          pg_index x
          JOIN pg_class c ON c.oid = x.indrelid
          JOIN pg_class i ON i.oid = x.indexrelid
          JOIN pg_namespace n ON n.oid = c.relnamespace
          LEFT JOIN pg_constraint co ON (co.conrelid = c.oid AND co.conindid = x.indexrelid)
        WHERE
          c.relkind = 'r'
          AND n.nspname = '#{schema}'
          AND c.relname = '#{table}'
          AND x.indisunique = false
          AND co.contype IS NULL
        ORDER BY
          i.relname;
      SQL
    end

    def index_exists?(schema, table, index_name)
      ActiveRecord::Base.connection.execute(<<-SQL.squish).values.flatten.first
        SELECT EXISTS (
          SELECT 1
          FROM pg_indexes
          WHERE schemaname = '#{schema}'
            AND tablename = '#{table}'
            AND indexname = '#{index_name}'
        )
      SQL
    end

    def create_index(schema, table, index_name, index_definition)
      # Extract the part of the index definition after "USING"
      index_type_and_columns = index_definition.split(' USING ').last

      ActiveRecord::Base.connection.execute(<<-SQL.squish)
        CREATE INDEX IF NOT EXISTS #{index_name} ON #{schema}.#{table} USING #{index_type_and_columns}
      SQL
    end

    temporal_tables = get_tables('temporal')

    temporal_tables.each do |table|
      puts "Processing table: #{table}"
      non_unique_indexes = get_non_unique_indexes('temporal', table)

      non_unique_indexes.each do |index_name, index_definition|
        if index_exists?('history', table, index_name)
          puts "  [SKIP] Index '#{index_name}' already exists in history.#{table}"
        else
          puts "  [CREATE] Creating index '#{index_name}' on history.#{table}"
          create_index('history', table, index_name, index_definition)
        end
      end
    end

    puts 'Index synchronization completed!'
  end
end