PostgreSQL adapter method .tables return table names without schema

[nibygro]

Take a look at #1154 issue of Sequel gem https://github.com/jeremyevans/sequel/issues/1154.

My database consists of multiple schemas with some number of tables with the same name, like this:

       Schema    |          Name           | Type    |  Owner   
-----------------+-------------------------+---------+----------
 type1           | tasks                   | table   | user
 type1           | updates                 | table   | user
 type2           | tasks                   | table   | user
 type2           | updates                 | table   | user
 type3           | tasks                   | table   | user
 type3           | updates                 | table   | user
  ...

I am using database_cleaner (1.5.1) and rspec (3.4.0) for my tests. Database config contains search_path for all the schemas. When I try to clear DB with :deletion method (or :truncate) - it didn't remove any records.

DatabaseCleaner[:sequel, { connection: DB }]

  config.before(:suite) do
    DatabaseCleaner.strategy = :transaction
    DatabaseCleaner.clean_with(:deletion)
  end

  config.around(:each) do |example|
    DatabaseCleaner.cleaning do
      example.run
    end
  end

Database contains all the data that was inserted during the tests. I open log files and see this:

I, INFO -- : (0.000349s) ALTER TABLE "updates" DISABLE TRIGGER ALL I, INFO -- : (0.000444s) ALTER TABLE "tasks" DISABLE TRIGGER ALL I, INFO -- : (0.000236s) ALTER TABLE "updates" DISABLE TRIGGER ALL I, INFO -- : (0.000232s) ALTER TABLE "tasks" DISABLE TRIGGER ALL I, INFO -- : (0.000202s) ALTER TABLE "updates" DISABLE TRIGGER ALL I, INFO -- : (0.000200s) ALTER TABLE "tasks" DISABLE TRIGGER ALL .... I, INFO -- : (0.000349s) DELETE FROM "tasks" I, INFO -- : (0.000444s) DELETE FROM "updates" I, INFO -- : (0.000249s) DELETE FROM "tasks" I, INFO -- : (0.000314s) DELETE FROM "updates" ...

As you can see - no schema provided to PSQL, just raw table names. Ok, I open Sequel adapter of database_cleaner and learned how it tries to delete the data from the tables:

    # database_cleaner/sequel/deleteion.rb : 30-46
    def delete_tables(db, tables)  # tables - connection.tables method of Sequel
      tables.each do |table|
        db[table.to_sym].delete
      end
    end

   # database_cleaner/sequel/truncation.rb : 64-67
    def tables_to_truncate(db)
       (@only || db.tables.map(&:to_s)) - @tables_to_exclude
    end

As we see, db_cleaner gem get tables name with sequel .tables method. Ok, go to Sequel code (adapters/shared/postgres.rb - 525 line of code) and see that tables method return table names WITHOUT schemas.

2.1.7 :004 > DB.tables # DB - sequel connection
 => [:updates, :tasks, :updates, :tasks, :updates, :schema_info, :tasks, :tasks, :tasks, :updates, :tasks, :updates]

Thats why no one record is removed and that is why database_cleaner doesn't remove any data.

For correct removing of the data from the tables with schema database_cleaner gem needs something like this:

[:type1__updates, :type1__tasks, :type2__updates, :type2__tasks, :type3__updates, :type3__tasks ]

Thanks!

[nibygro]

I found that the same problem with tables names was discussed 2 years ago (see https://www.bountysource.com/issues/1239086-sequel-postgresql-truncate-strategy-fails-if-there-s-tables-in-other-schemas), but the solution (use %only option) is wrong. I think we need to rewrite some Sequel part of code and then use it in database_cleaner.

[msmith1114]

Im running into the same problem. Using the %only option isn't really ideal because we have MULTIPLE table names, also I believe you need to include the schema.table_name even using the %only option, since otherwise it seems to throw it to 'delete' which is just deleting the entire table for me.