PG::InFailedSqlTransaction on creating initial tenant

Question

I'm converting my Rails project to multi-tenant and I'm using Apartment.

I followed the directions on their README and the video they link to

My apartment.rb looks like this:

require 'apartment/elevators/subdomain'
Apartment.configure do |config|
  config.excluded_models = ['Project', 'User']
  config.tenant_names = lambda { Project.pluck :subdomain }
  config.use_schemas = true
  config.persistent_schemas = %w{ public }
end
Rails.application.config.middleware.use Apartment::Elevators::Subdomain

My Project model looks like this:

class Project < ApplicationRecord
  after_create :create_tenant

  private

  def create_tenant
    Apartment::Tenant.create(subdomain)
  end
end

I can run db:migrate and db:setup no problem. It creates all my tables in the public schema then.

But, when I try to create a new Project, I get this (from rails c)

C:\code\vulnerability-history>rails c
Loading development environment (Rails 5.0.1)
irb(main):001:0> Project.create(name: 'foo', subdomain: 'bar')
   (0.0ms)  BEGIN
  SQL (1.0ms)  INSERT INTO "public"."projects" ("name", "subdomain") VALUES ($1, $2) RETURNING "id"  [["name", "foo"], ["subdomain", "bar"]]
   (1.0ms)  CREATE SCHEMA "bar"
  SQL (1.0ms)  CREATE EXTENSION IF NOT EXISTS "plpgsql"
   (2.0ms)  DROP TABLE "commit_filepaths" CASCADE
   (14.0ms)  CREATE TABLE "commit_filepaths" ("id" serial primary key, "commit_id" integer NOT NULL, "filepath_id" integer NOT NULL, "total_churn" integer DEFAULT 0 NOT NULL)
   (1.0ms)  DROP TABLE "commits" CASCADE
   (11.0ms)  CREATE TABLE "commits" ("id" serial primary key, "commit_hash" character varying NOT NULL, "author_id" integer NOT NULL, "message" character varying NOT NULL, "date_created" timestamp NOT NULL, "notes" jsonb DEFAULT '{}' NOT NULL)
   (1.0ms)  DROP TABLE "developers" CASCADE
   (11.0ms)  CREATE TABLE "developers" ("id" serial primary key, "email" character varying NOT NULL)
   (1.5ms)  DROP TABLE "events" CASCADE
   (9.4ms)  CREATE TABLE "events" ("id" serial primary key, "detail_type" character varying NOT NULL, "detail_id" integer NOT NULL, "title_template" character varying DEFAULT ':title:' NOT NULL, "description_template" character varying DEFAULT ':description:' NOT NULL, "type_template" character varying DEFAULT ':type:' NOT NULL, "date_template" character varying DEFAULT ':date:' NOT NULL, "style_id" integer NOT NULL)
   (1.0ms)  DROP TABLE "filepaths" CASCADE
   (11.0ms)  CREATE TABLE "filepaths" ("id" serial primary key, "filepath" character varying NOT NULL, "notes" jsonb DEFAULT '{}' NOT NULL)
   (1.0ms)  DROP TABLE "fixes" CASCADE
   (11.0ms)  CREATE TABLE "fixes" ("id" serial primary key, "commit_id" integer NOT NULL, "vulnerability_id" integer NOT NULL, "notes" jsonb DEFAULT '{}' NOT NULL)
   (1.0ms)  DROP TABLE "projects" CASCADE
   (13.0ms)  CREATE TABLE "projects" ("id" serial primary key, "name" character varying NOT NULL, "subdomain" character varying NOT NULL)
   (1.0ms)  DROP TABLE "releases" CASCADE
   (10.6ms)  CREATE TABLE "releases" ("id" serial primary key, "number" integer NOT NULL, "date_released" timestamp NOT NULL, "project" character varying NOT NULL, "notes" jsonb NOT NULL)
   (1.0ms)  DROP TABLE "styles" CASCADE
   (11.3ms)  CREATE TABLE "styles" ("id" serial primary key, "name" character varying NOT NULL, "color" character varying DEFAULT '#ffffff' NOT NULL, "icon" character varying DEFAULT 'stars' NOT NULL)
   (1.0ms)  DROP TABLE "users" CASCADE
   (11.4ms)  CREATE TABLE "users" ("id" serial primary key, "username" character varying NOT NULL)
   (1.0ms)  DROP TABLE "vccs" CASCADE
   (10.0ms)  CREATE TABLE "vccs" ("id" serial primary key, "commit_id" integer NOT NULL, "vulnerability_id" integer NOT NULL, "notes" jsonb DEFAULT '{}' NOT NULL)
   (1.0ms)  DROP TABLE "vulnerabilities" CASCADE
   (12.3ms)  CREATE TABLE "vulnerabilities" ("id" serial primary key, "cve" character varying NOT NULL, "announced" timestamp NOT NULL, "description" character varying DEFAULT '' NOT NULL, "notes" jsonb DEFAULT '{}' NOT NULL)
   (1.0ms)  SELECT version FROM "schema_migrations"
  ActiveRecord::InternalMetadata Load (1.0ms)  SELECT  "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2  [["key", :environment], ["LIMIT", 1]]
   (0.0ms)  ROLLBACK
ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
: SET search_path TO "public", "public"
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:589:in `block in log'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activesupport-5.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql/schema_statements.rb:315:in `schema_search_path='
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/apartment-1.2.0/lib/apartment/adapters/postgresql_adapter.rb:40:in `reset'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/apartment-1.2.0/lib/apartment/adapters/abstract_adapter.rb:109:in `rescue in ensure in switch'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/apartment-1.2.0/lib/apartment/adapters/abstract_adapter.rb:109:in `ensure in switch'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/apartment-1.2.0/lib/apartment/adapters/abstract_adapter.rb:109:in `switch'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/apartment-1.2.0/lib/apartment/adapters/abstract_adapter.rb:26:in `block in create'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activesupport-5.0.1/lib/active_support/callbacks.rb:97:in `__run_callbacks__'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activesupport-5.0.1/lib/active_support/callbacks.rb:750:in `_run_create_callbacks'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activesupport-5.0.1/lib/active_support/callbacks.rb:90:in `run_callbacks'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/apartment-1.2.0/lib/apartment/adapters/abstract_adapter.rb:23:in `create'
... 22 levels...
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/transactions.rb:211:in `transaction'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/transactions.rb:392:in `with_transaction_returning_status'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/transactions.rb:319:in `block in save'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/transactions.rb:334:in `rollback_active_record_state!'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/transactions.rb:318:in `save'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/suppressor.rb:41:in `save'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/activerecord-5.0.1/lib/active_record/persistence.rb:34:in `create'
        from (irb):1
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/railties-5.0.1/lib/rails/commands/console.rb:65:in `start'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/railties-5.0.1/lib/rails/commands/console_helper.rb:9:in `start'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/railties-5.0.1/lib/rails/commands/commands_tasks.rb:78:in `console'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/railties-5.0.1/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
        from C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/railties-5.0.1/lib/rails/commands.rb:18:in `<top (required)>'
        from bin/rails:4:in `require'
        from bin/rails:4:in `<main>'

I did notice this issue with a similar stacktrace, but the workaround doesn't make sense to me.

I'm using: Ruby 2.3.1 (2016-04-26 patchlevel 112) [x64-mingw32] apartment 1.2.0 Rails 5.0.1

Try removing all but one table from your tenant migrations to narrow down the issue. It may be one bad migration. From the ActiveRecord::StatementInvalid error, it means one of the SQL statements is invalid. — Troy, Feb 28 '17 at 04:24

score 0 · Answer 1 · answered Feb 28 '17 at 09:28

0

If the log is to be trusted, it appears to be failing on:

SELECT  "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2  [["key", :environment], ["LIMIT", 1]]

Does your db/schema.rb contain this table? It's a new system table in Rails 5.

answered Feb 28 '17 at 09:28

Mike Campbell

7,921
2
38
51

Ah! I didn't realize that one was new. So when I do not use apartment, it gets created just fine. But it appears Apartment fails to create it? I'll go file a bug with them and confirm. If that's the issue I'll come back and award you. – Andy Feb 28 '17 at 14:59
No word from Apartment folks yet on [my issue](https://github.com/influitive/apartment/issues/404), but it looks like they are aware of ar_internal_metadata with [this commit](https://github.com/influitive/apartment/commit/bd3f56de3d5842ba333fa82f2dab9e65b7ec5592) – Andy Feb 28 '17 at 17:13
i am an apartment folk! :) the new tenant gets created from your db/schema.rb, is this table defined there? how're you creating when you're not using apartment and it's working? edit: nvm i've forgotten how rails works momentarily. i'll look in to it. – Mike Campbell Feb 28 '17 at 17:21
Great! Thank you for working on this. So without apartment, I do get ar_internal_metadata created. With apartment installed and just doing a rails db:setup I get all of the tables from schema.rb, including ar_internal_metadata. (BTW my code on GitHub is [here in the issue-121 branch](https://github.com/andymeneely/vulnerability-history/blob/issue-121/db/schema.rb) in case you need it – Andy Mar 03 '17 at 21:46

PG::InFailedSqlTransaction on creating initial tenant

1 Answers1