Adding multi_server_setup to already established application

[tjhanley]

Steps to reproduce

When I run Apartment::Tenant.create('tenant_2') The connection is established to the new Database Host/DB and the schema "spaces" are created for public and tenant_2, however no tables are created.

If I add a new tenant with single database server setup the schema "space" is created and the tables are defined.

Expected behavior

I expect it to create the tables on the new server.

Actual behavior

Apartment::Tenant.create('tenant_2')
  [app_development] [tenant_1]    (24.8ms)  BEGIN;
          CREATE SCHEMA "tenant_2";
          COMMIT;
/Users/tom/.rbenv/versions/3.0.4/lib/ruby/gems/3.0.0/gems/ros-apartment-2.11.0/lib/apartment/adapters/postgresql_adapter.rb:139:in `raise_schema_connect_to_new': Could not set search path to schemas, they may be invalid: "tenant_2" "tenant_1". (Apartment::TenantNotFound)
Original error: ActiveRecord::StatementInvalid: Could not find schema tenant_2
/Users/tom/.rbenv/versions/3.0.4/lib/ruby/gems/3.0.0/gems/ros-apartment-2.11.0/lib/apartment/adapters/postgresql_adapter.rb:75:in `connect_to_new': Could not find schema tenant_2 (ActiveRecord::StatementInvalid)

Executing Apartment::Tenant.drop('tenant_2') succeeds in dropping the tenant from the new server.

Listing the schemas on each server

Server 1

SELECT schema_name
FROM information_schema.schemata;

schema_name
pg_toast
pg_catalog
public
information_schema
tenant_1

Server 2 [New]

SELECT schema_name
FROM information_schema.schemata;

schema_name
pg_catalog
information_schema
public
tenant_2

System configuration

• Database: pg 14

• Apartment version: 2.11.0

• Apartment config (in config/initializers/apartment.rb or so):

  • use_schemas: true
  • config.with_multi_server_setup = true

  config.tenant_names = {
    'tenant_1' => {
      host: 'some-other-ip',
      adapter:  'postgresql',
      database: 'app_development',
      username: '[redacted]',
      port: 5432,
      encoding: 'unicode',
      password: '[redacted]'
    },
    'tenant_2' => {
      host: 'some-other-ip',
      adapter:  'postgresql',
      database: 'app_development',
      username: '[redacted]',
      port: 5432,
      encoding: 'unicode',
      password: [redacted]
    }
  }

• Rails (or ActiveRecord) version: 6.1.7

• Ruby version: 3.04

[criccomini]

We have investigated this issue. The problem appears to be in the connect_to_new method in postgresql_adapter.rb. Upon debugging, we find that the connection config is always pointing to the default server defined in database.yml.

We then compared the connect_to_new method in the PG adapter with the same method in abstract_adapter.rb. The abstract connect_to_new method appears to switch to the new connection:

      #   Connect to new tenant
      #
      #   @param {String} tenant Database name
      #
      def connect_to_new(tenant)
        return reset if tenant.nil?

        query_cache_enabled = ActiveRecord::Base.connection.query_cache_enabled

        Apartment.establish_connection multi_tenantify(tenant)
        Apartment.connection.active? # call active? to manually check if this connection is valid

        Apartment.connection.enable_query_cache! if query_cache_enabled
      rescue *rescuable_exceptions => e
        Apartment::Tenant.reset if reset_on_connection_exception?
        raise_connect_error!(tenant, e)
      end

The switch happens in the establish_connection call of the method above.

Thus, we believe the error is that connect_to_new in the PG adapter has this behavior missing. To verify, we added:

      #   Set schema search path to new schema
      #
      def connect_to_new(tenant = nil)
        return reset if tenant.nil?

        Apartment.establish_connection multi_tenantify(tenant, false)
        Apartment.connection.active? # call active? to manually check if this connection is valid
        
        raise ActiveRecord::StatementInvalid, "Could not find schema #{tenant}" unless schema_exists?(tenant)

        @current = tenant.is_a?(Array) ? tenant.map(&:to_s) : tenant.to_s
        Apartment.connection.schema_search_path = full_search_path

        # When the PostgreSQL version is < 9.3,
        # there is a issue for prepared statement with changing search_path.
        # https://www.postgresql.org/docs/9.3/static/sql-prepare.html
        Apartment.connection.clear_cache! if postgresql_version < 90_300
      rescue *rescuable_exceptions => e
        raise_schema_connect_to_new(tenant, e)
      end

To the PG adapter. This appears to have fixed the problem.

Note: We used multi_tenantify(tenant, false) because we want to use the DB name defined in the config.tenant_names hashmap rather than the default behavior (which uses the tenant's name as the DB name).

[criccomini]

I also want to note that this only appears to be an issue when one is using Apartment with:

• Postgres
• Multiple hosts
• Apartment.use_schemas = true