CODEWITHSUNDEEP
postgresql
kubal5003
kubal5003

Reputation: 7254

Sorting tables by Master-Detail Relation

I need to delete all data from all tables each and every time a Unit Test is fired. Right now I'm using TRUNCATE TABLE "table_name" CASCADE for each table that is included in the test suite.

The problem is that those queries take a lot of time - about 9-11 seconds!

I would like to be able to replace those TRUNCATE...CASCADE with just TRUNCATE statements, but to do that I need a list of tables ordered by the master-detail (parent-child) relationship (children first). Manually I could create such a list once or twice, but the database changes everyday. Is there a way to generate a list of tables in that order?

I'm using Postgresql.

Upvotes: 0

Views: 136

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

The way I would look at resolving this would be as follows:

  1. Defer all deferrable constraints and ignore them. You can't effectively have cycles in non-deferrable foreign keys because there is no order of insert that will satisfy them

  2. I would build a stored procedure which would look up deferrable foreign keys and defer them all. This is relatively trivial.

  3. I would build a CTE which would build a tree of foreign key constraints and then search through them reversing the order by length of the path to reach them, so that the first tables would be the ones which had the most tables depending on their keys.

However this may be overkill. It may be easier to just have two functions:

  1. Function 1 looks through every relation in a class and for every relation, issues:

    EXECUTE $e$ ALTER TABLE $e$ || quote_ident(relname) || $e$ disable trigger all $e$;

  2. Function 2 does the reverse:

    EXECUTE $e$ ALTER TABLE $e$ || quote_ident(relname) || $e$ enable trigger all $e$;

Call function 1 at the beginning of the clear process. Call function 2 just before committing.

Upvotes: 1

Related Questions