Closed tweavers closed 3 weeks ago
Thanks for raising this issue and doing that research @tweavers !
Does your example project consistently work if you add the following macro override to your project in order to set dstring=True
?
macros/overrides/make_backup_relation.sql
{% macro postgres__make_backup_relation(base_relation, backup_relation_type, suffix) %}
{% set backup_relation = postgres__make_relation_with_suffix(base_relation, suffix, dstring=True) %}
{{ return(backup_relation.incorporate(type=backup_relation_type)) }}
{% endmacro %}
The following setup was able to yield the same error you reported a majority of the time (but not every single time).
But when I added dstring=True
like https://github.com/dbt-labs/dbt-postgres/issues/20#issuecomment-1959626260, then it worked consistently.
@dbeatty10 - appreciate the quick follow up. Confirming that the override macro is working - thank you!.
We're seeing the backup tables now named like this:
rename to "model_with_a_very_long_name_that_we_mad__dbt_backup101508522444"
Interestingly, the __dbt_tmp
models aren't appended with the dtstring
despite the parameters in postgres__make_temp_relation
. I am surprised this doesn't throw an error that the table already exists. I've tested setting dstring
to false in a similar manner and they are named the same. Example below and also captured in earlier posted logs
model_with_a_very_long_name_that_we_made_for_some_reas__dbt_tmp
Here's the only difference between dstring=False
and dstring=True
:
21c21
< alter table "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_reason_a" rename to "model_with_a_very_long_name_that_we_made_for_some_r__dbt_backup"
---
> alter table "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_reason_a" rename to "model_with_a_very_long_name_that_we_mad__dbt_backup135949342860"
26c26
< drop table if exists "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_r__dbt_backup" cascade
---
> drop table if exists "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_mad__dbt_backup135949342860" cascade
When dstring=False
, it looks like the ...__dbt_backup
table is created in one transaction and then dropped in a different one. So that could explain a collision between the transactions in the different threads.
Here is a lightly formatted version of the queries for dstring=False
from the script below:
BEGIN
create table "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_reas__dbt_tmp" as (
-- depends on:
-- "postgres"."dbt_dbeatty"."empty_lookup_table"
select 'a' as id
);
alter table "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_reason_a" rename to "model_with_a_very_long_name_that_we_made_for_some_r__dbt_backup"
alter table "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_reas__dbt_tmp" rename to "model_with_a_very_long_name_that_we_made_for_some_reason_a"
COMMIT
drop table if exists "postgres"."dbt_dbeatty"."model_with_a_very_long_name_that_we_made_for_some_r__dbt_backup" cascade
This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.
Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.
Is this a new bug?
Current Behavior
dbt runs will occasionally fail due to
__dbt_backup
table names not being unique. This happens when we have very similarly named models (aka share the first 51+ characters) that are being updated at the same time in a multi-threaded run.These are shown by dbt as
database errors
Expected Behavior
__dbt_backup
table names should incorporate a unique identifier as part of the suffix to handle similarly named models. The adapter macro has a mechanism in place for this already with thedstring
parameter. This paramter is set to true for temporary tables but not backup tables.Relevant code block
Steps To Reproduce
dbt --log-level debug run --select <model_names> --threads 4
Relevant log output
Environment
Additional Context
No response