Issues with truncate_table when using referential integrity and transactions in PostgreSQL

[tovodeverett]

I ran into some issues with the behavior of SerializationHelper::Load.truncate_table when using yaml_db on a PostgreSQL database that had referential integrity. In order to assist in resolving the referential integrity issues, I needed to add CASCADE to the TRUNCATE statement. In order to get the rescue block to work within a transaction, I had to add SAVEPOINT and ROLLBACK statements. My monkey-patch only works for PostgreSQL, and may not work if you're not within a transaction, so getting this ready for inclusion into the actual code base would require adding database-specific support to yaml_db as well as transaction detection logic. I'd be happy to work on a pull request, but before I do I'd like to see some consensus on the desirability of resolving this issue and some discussion of approaches (should I embed the database-specific code in truncate_table or should I enable more generic support for database-specific code across the code base).

Here's my monkey-patch:

module SerializationHelper
  class Load
    def self.truncate_table(table)
      begin
        ActiveRecord::Base.connection.execute("SAVEPOINT before_truncation")
        ActiveRecord::Base.connection.execute("TRUNCATE #{SerializationHelper::Utils.quote_table(table)} CASCADE")
      rescue Exception
        ActiveRecord::Base.connection.execute("ROLLBACK TO SAVEPOINT before_truncation")
        ActiveRecord::Base.connection.execute("DELETE FROM #{SerializationHelper::Utils.quote_table(table)}")
      end
    end
  end
end

[mazikwyry]

I have the same problem. PostgreSQL doesn't allow to add data to even empty table if it has foreign key, because of TRUNCATE. I think TRUNCATE should be optional, cos in most cases this gem is used to migrate databases.

You can skip truncate by adding this to f.e. config/application.rb:

module SerializationHelper
  class Load
    def self.truncate_table(table)
     true
    end
  end
end

[nruth]

I also think it should be optional. My use-case is migrating mysql to postgres, and I'm loading the yml files into an empty db I just created, no need to truncate.

It should be easy to add to the code, as it's already an option in all of the load functions. They just default the argument to true, and none of the rake tasks actually pass it an argument (it could be an env var).

Or we can just write a task in our application, using the provided tasks as a template, using the same helpers.

[alistairholt]

+1