Table materialization is not using make_temp_relation or alias as identifier

[sumit0k]

Describe the bug

Refering this particular file, it is present in new versions as well
https:/fishtown-analytics/dbt/blob/0.14.latest/core/dbt/include/global_project/macros/materializations/table/table.sql

The first 3 lines are, where for tmp and backup table, model['name'] is used instead of model['identifier']

  {%- set identifier = model['alias'] -%}
  {%- set tmp_identifier = model['name'] + '__dbt_tmp' -%}
  {%- set backup_identifier = model['name'] + '__dbt_backup' -%}

This results into failure while running the same model with different alias. Use usually do this to do backfilling, where our temporary tables are with alias this.table ~ '_backfill'. These model have same name as regular models but their aliases are different.

Steps To Reproduce

1. Create a model with table materialization
2. Create another project with same model name but with alias as this.table ~ '_backfill',. Sample config

{{
    config({
        "alias": this.table ~ '_backfill',
        "materialized": "table"
    })
}}

3. Run both projects together.
4. DBT would create same table in both runs with same name instead of using expected alias as prefix.

Expected behavior

Both run should finish successfully as their aliases are different and they are working on to create different table in database.

Proposed solution

make_temp_relation suffixes a timestamp with temp table name and that has solved this problem in our custom materializations. We are using something like

{%- set tmp_source_relation = make_temp_relation(target_relation, tmp_source_suffix) -%}

OR

Using this code to set the tmp_identifier would solve too. Although it is less resilient.

{%- set tmp_identifier = model['alias'] + '__dbt_tmp' -%}

Screenshots and log output

My model render failed with

2020-04-18 05:15:23,268 (Thread-6): On pm_transient___serverlogs_event: create  table
    "database_name"."schema_name"."pm_transient___serverlogs_event__dbt_tmp"
.......
2020-04-18 05:15:46,055 (Thread-6): Postgres error: duplicate key violates unique constraint "pg_class_relname_nsp_index" (possibly caused by concurrent transaction conflict)

2020-04-18 05:15:46,055 (Thread-6): On pm_transient___serverlogs_event: ROLLBACK

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:
I am running this in DBT Cloud (v0.14.4)

installed version: 0.14.4
   latest version: 0.16.1

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

The operating system you're using:
MacOS Catalina v10.15.4

The output of python --version:
Python 3.7.0

Additional context

Nope

[drewbanin]

Thanks for the report @sumitkumar1209!

Sure, I think using make_temp_relation is a good idea -- we use it in other materializations in dbt with a some success :)

The one thing to watch out for there is going to be #2197 -- we hit this bug in the make_temp_relation implementation on Postgres, and I think it might be possible to see the same behavior on Redshift too.

[sumit0k]

Yeah, understand that. That bug is anyway present for other materialization.

[github-actions]

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 remove the stale label or comment on the issue, or it will be closed in 7 days.