Tables created in complex queries are not cleaned

[joshleaves]

This is an example of a query we have, using PostGreSQL's WITH statement:

WITH updated_records AS (
  UPDATE records SET updated_at = NOW(), foo = 'bar'
  WHERE type = 'custom_type'
  RETURNING id
)
INSERT INTO record_histories (record_id, action)
SELECT
  id AS record_id,
  'set_to_bar' AS action
FROM updated_records

Breaking it down into one UPDATE records... and one INSERT INTO record_histories.. queries would solve the issue but we'd like to avoid this solution if possible and keep our current codebase as is.

For minimal break of codebase, possible solutions rely on modifying the matcher regexp to either remove the leading \A (but then every test query that includes INSERT INTO xxxxx will add xxxxx to the tables to clean) or adding support for WITH xxxxx AS (...) INSERT INTO...-style queries to the regexp (which sounds dauting enough).

What would be your thoughts?

[deeeki]

Though I didn't know WITH statement, I think both solutions are OK. You can send your Pull request.

[dillonwelch]

Possibly related, I have a table being populated by raw SQL that isn't being picked up by DatabaseRewinder. The SQL goes something like

delete from table_name where model_id = 1;

insert into table_name(
  column_a,
  column_b,
  ...
)
select
  column_a,
  column_b,
  ...
from 
  complicated_sql