[CT-3473] Deadlocks in postgres related to drop cascade of __dbt_backup tables with downstream dependencies.

[sheldonkhall]

I have started to see this issue occurring in the last month. As @j-hulbert describes dbt runs a drop cascade of tables with __dbt_backup appended as part of the run command. My specific situation can be seen in the image. Randomly I will get my postgres log spitting out an error related to a deadlock:

DETAIL: Process 9906 waits for AccessExclusiveLock on object 422131525 of class 2618 of database 16404; blocked by process 9902.
Process 9902 waits for AccessExclusiveLock on relation 422131522 of database 16404; blocked by process 9906.
Process 9906: /* {"app": "dbt", "dbt_version": "1.7.3", "profile_name": "data_models", "target_name": "prod", "node_id": "model.data_models.accounts_view"} */
drop table if exists "analyticsdb"."mvs__CANDIDATE__3f210b4"."accounts_view__dbt_backup" cascade
Process 9902: /* {"app": "dbt", "dbt_version": "1.7.3", "profile_name": "data_models", "target_name": "prod", "node_id": "model.data_models.ref_institutional_accounts"} */
drop table if exists "analyticsdb"."mvs__CANDIDATE__3f210b4"."ref_institutional_accounts__dbt_backup" cascade

It seems that even though dbt captures the DAG and therefore could know there is a shared downstream dependency. It still tries to drop cascade two tables that have the same downstream dependency. Because of the parallelism there is a chance of a deadlock and I see this in practice regularly enough to be a problem.

Originally posted by @sheldonkhall in #2801 (comment)

[EitanKatsightfull]

Subscribing to this

[scottgigante]

I am having the same issue.

[grigsos]

Was having this issue, due to non native use of foreign keys, with either personal macros or the snowflake package. As soon as I switched to new constraints (v1.9) the issue has disappeared.