Sequel+postgresql truncate strategy fails if there's tables in other schemas

[joevandyk]

If I have a table in the "audit" schema on postgresql, using the Sequel truncate strategy will fail. A quick review of the code shows something like this is happening:

execute "truncate #{ db.tables.join(', ')"

db.tables will return a list of all the tables without being schema-qualified. So if I have public.orders and audit.logged_actions, this gets ran: truncate orders, logged_actions which will fail.

A possible solution is to get this SQL running instead: truncate public.orders, audit.logged_actions

[joseluistorres]

I'm having issues to truncate tables in different schemas but the same DBs as well, is this even supported? cc @joevandyk @bmabey

[rubiii]

same question.

[joseluistorres]

Ok nevermind, it worked for me in rspec like this:

# spec_helper
config.before :suite do
  DatabaseCleaner.strategy = :truncation, {:only => %w[my_table_in_schema1 my_table_in_schema2]}
end

config.after :suite do
  DatabaseCleaner.clean
end

I also included a test code in the source:

# ... postgresql_setup.rb
def load_other_schema
    ActiveRecord::Schema.define do
      execute <<-SQL
      DO $$
      BEGIN

          IF NOT EXISTS(
              SELECT schema_name
                FROM information_schema.schemata
                WHERE schema_name = 'my_schema'
            )
          THEN
            EXECUTE 'CREATE SCHEMA my_schema';
          END IF;

      END
      $$;

      SET search_path = my_schema, pg_catalog;

      SQL
      create_table 'my_schema.posts', :force => true do |t|
        t.string :title
      end
      execute <<-SQL
        SET search_path = my_schema, public;
      SQL
    end
  end

# ... postgresql_spec.rb
it "truncates the table in a different SCHEMA" do
  2.times { Post.create(:title => 'hello') }
  Post.count.should eq 2
  connection.truncate_table('posts')
  Post.count.should eq 0
end

Testing:

bundle exec rspec spec/database_cleaner/active_record/truncation/postgresql_spec.rb 
Your Gemfile lists the gem sqlite3 (>= 0) more than once.
You should probably keep only one of them.
While it's not a problem now, it could cause errors if you change the version of just one of them later.
Active Record 3.2.11, pg

-- create_table(:users, {:force=>true})
   -> 0.0158s
-- execute("      DO $$\n      BEGIN\n\n          IF NOT EXISTS(\n              SELECT schema_name\n                FROM information_schema.schemata\n                WHERE schema_name = 'my_schema'\n            )\n          THEN\n            EXECUTE 'CREATE SCHEMA my_schema';\n          END IF;\n\n      END\n      $$;\n\n      SET search_path = my_schema, pg_catalog;\n\n")
   -> 0.0030s
-- create_table("my_schema.posts", {:force=>true})
   -> 0.0034s
-- execute("        SET search_path = my_schema, public;\n")
   -> 0.0002s
  #truncate_table
    truncates the table
    resets AUTO_INCREMENT index of table
    truncates the table in a different SCHEMA
  behaves like an adapter with pre-count truncation
    #pre_count_truncate_tables
      with :reset_ids set true
        truncates the table
        resets AUTO_INCREMENT index of table
      with :reset_ids set false
        truncates the table
        does not reset AUTO_INCREMENT index of table

Finished in 0.36208 seconds
7 examples, 0 failures

[rubiii]

we’re running all specs in a transaction now, which works just fine.

around do |example|
  connection = Sequel.connect(config)
  connection.transaction do
    example.run
    # force rollback
    raise Sequel::Error::Rollback
  end
end

[joseluistorres]

nice :+1: