Can't delete a data source if one of the queries references query result from it but uses a different data source

[arikfr]

Issue Summary

When you have the following situation:

Query A references query result from data source A, but itself references data source B.

You will not be able to delete data source A, because it will fail to delete the query results using it.

Steps to Reproduce

The way the above can happen is if:

1. You execute query A while it uses data source A and update it to use data source B.
2. If the data source update happens right after this query was executed but before we actually update the query result reference.

It's an edge case, but happened already twice for people who reported it.

The correct solution here is to update the latest_query_data_id reference to get nullified if the query result is deleted.

Technical details:

• Redash Version: 7

[rauchy]

Feels like the proper way to handle this would be to modify the DB constraint to ON DELETE SET NULL, right?

[arikfr]

@rauchy yes, but I'm not sure I would go for DB constraint at this point, but rather just use SQLA's relationship/backref cascade options.

[rauchy]

Yeah, I tried playing around with relationship/backref cascades but stumbled on

IntegrityError: (psycopg2.IntegrityError) update or delete on table "query_results" violates foreign key constraint "queries_latest_query_data_id_fkey" on table "queries"`

which I'm guessing is due to the fact that queries_latest_query_data_id_fkey exists as a non-cascading foreign key in the first place?

Anyway, this does the trick. Let me know if you think I missed something and should revisit the ORM approach, or submit the db-level solution PR.

[arikfr]

Anyway, this does the trick. Let me know if you think I missed something and should revisit the ORM approach, or submit the db-level solution PR.

I'm worried about the implications of running this migration on a busy instance 🤔

[rauchy]

Yeah I'm unsure of this as well. Is there any other approach you can think of?

[spacentropy]

Happened for us.

DETAIL:  Key (id)=(3101) is still referenced from table "queries".
 [SQL: 'DELETE FROM query_results WHERE %(param_1)s = query_results.data_source_id'] [parameters: {'param_1': 8}] (Background on this error at: http://sqlalche.me/e/gkpj)
[2019-08-12 14:50:00,046][PID:14][ERROR][redash] Exception on /api/data_sources/8 [DELETE]```