`add/remove_foreign_key` do not work on temporal tables

[tagliala]

add_foreign_key :countries, :regions

== 20220724104350 AddRegionFkConstraints: migrating ===========================
-- add_foreign_key(:countries, :regions)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::WrongObjectType: ERROR:  "countries" is not a table or foreign table

[tagliala]

Unfortunately

      def add_foreign_key(table_name, *)
        return super unless is_chrono?(table_name)
        on_temporal_schema { super }
      end

will not work in this case because if you want to add a foreign key between a temporal table and a normal table, on_temporal_schema will assume that both tables are temporal, but this cannot be true, so this requires some kind of custom logic that I cannot figure out at the moment

[Zyndoras]

Is there currently any other way of adding a reference to a temporal: true-table aside from dropping and recreating the table?

[Martin-Alexander]

I'm assuming the expected behaviour is that if from_table or to_table is a Chrono table, then it's the temporal table that should be the referring/referenced table.

A foreign key constraint on a history table seems bad.

A foreign key constraint that references a history table seems fine, but that'd be kinda weird. So I assume it's fine to not support that.

Also, from reading the code it seems this issue only applies to the following methods:

• add_foreign_key / remove_foreign_key
• add_references / remove_references
• add_belongs_to / remove_belongs_to

And not create_table or change_table.

As a side note, it seems like this issue also applies to add_check_constraint and remove_check_constraint.

[Martin-Alexander]

I think one workaround (which should work for managing indices and other constraints) is this:

create_table :products, temporal: true

create_table :customers

create_table :subscriptions, temporal: true do
  t.references :customer
  t.references :product
end

create_table :prices do |t|
  t.references :product
end

# From plain table to temporal table
add_foreign_key :prices, "temporal.products"

# From temporal table to temporal table
add_foreign_key "temporal.subscriptions", "temporal.products"

# From temporal table to plain table
add_foreign_key "temporal.subscriptions", :customers

But this technique won't work for add_reference :table, :ref, foreign_key: true since the first argument is used to create columns and indices in addition to foreign keys.

[Martin-Alexander]

The only solution I could think of for this method is:

def add_foreign_key(from_table, to_table, **options)
  return super unless is_chrono?(from_table) || is_chrono?(to_table)

  if is_chrono?(from_table) && is_chrono?(to_table)
    on_temporal_schema { super }
  else
    # One table belongs to the temporal schema and one to the default (probably public) schema

    # If objects cannot be found in the temporal schema, PG will fall back on the schema search path
    # See: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
    on_schema("#{TEMPORAL_SCHEMA},#{schema_search_path}") { super }
  end
end

But this seems a bit hacky since it relies on schema_search_path containing the default schema which won't be true if this is called from within on_temporal_schema