使用Apartment+Devise创建租户失败，报PG事务错误求助

Hey there! Let's work through this multi-tenancy hiccup you're hitting with Apartment and Devise. That PG::InFailedSqlTransaction error usually means a prior SQL operation failed, leaving the transaction in a broken state—so when Apartment tries to run SET search_path TO "public", PostgreSQL blocks it until the transaction is resolved. Here's how to fix it:

1. Fix the Tenant & User Creation Order + Transaction Isolation #

Devise's RegistrationsController#create wraps the user creation in a transaction by default. If you try to create a tenant inside that transaction and something fails, the whole transaction gets marked as invalid, triggering the error you see. Instead, split the logic: create the tenant first (outside Devise's transaction), switch to it, then create the user.

Update your RegistrationsController like this:

class RegistrationsController < Devise::RegistrationsController
  def create
    # First, create the tenant outside Devise's transaction
    tenant = Tenant.new(subdomain: params[:user][:subdomain])
    
    if tenant.save
      # Switch to the new tenant before creating the user
      Apartment::Tenant.switch!(tenant.subdomain)
      # Now let Devise handle user creation in the correct schema
      super
    else
      # Handle tenant creation failures cleanly
      flash[:alert] = "Couldn't create tenant: #{tenant.errors.full_messages.join(', ')}"
      redirect_to new_user_registration_path
    end
  end
end

2. Double-Check Your Apartment Configuration #

Make sure your config/initializers/apartment.rb is set up to keep the Tenant model in the public schema (so you can access it without switching tenants):

Apartment.configure do |config|
  # Keep Tenant model in public schema
  config.excluded_models = ["Tenant"]
  # Dynamically fetch tenant subdomains
  config.tenant_names = -> { Tenant.pluck(:subdomain) }
  # Ensure public schema stays accessible
  config.persistent_schemas = %w(public)
end

3. Handle Transaction Rollbacks Explicitly #

If a tenant creation does fail, make sure you don't leave the database in a broken transaction state. Add a rescue block to clean up:

def create_tenant(subdomain)
  begin
    Apartment::Tenant.create(subdomain)
  rescue ActiveRecord::StatementInvalid => e
    # Roll back the stuck transaction
    ActiveRecord::Base.connection.rollback_transaction
    # Re-raise or handle the error as needed
    raise "Tenant creation failed: #{e.message}"
  end
end

4. Verify Devise Callback Timing #

Ensure you're not running any Devise callbacks that try to access tenant-specific data before switching to the correct schema. For example, avoid user validation logic that queries tenant tables until after you've switched to the new tenant.

The root issue here is that a failed operation left your PostgreSQL transaction in an aborted state, blocking Apartment's SET search_path command. By isolating tenant creation from Devise's transaction and ensuring clean error handling, you should be able to resolve this.

内容的提问来源于stack exchange，提问作者Jeremy Bray