Dbt: Relation name '*__dbt_tmp' is longer than 63 characters

[moltar]

Describe the bug

My view name is pretty long, but it is less than 63 characters.

When dbt adds the __dbt_tmp suffix, it goes over the limit of 63 chars.

The following error is thrown by PostgreSQL:

Relation name 'foo__dbt_tmp' is longer than 63 characters

Steps To Reproduce

1. Use a view name that is 63 characters long.
2. dbt run

Expected behavior

To work anyways.

Screenshots and log output

See above.

System information

Which database are you using dbt with?

• [x] postgres
• [ ] redshift
• [ ] bigquery
• [ ] snowflake
• [ ] other (specify: ____________)

The output of dbt --version:

Using Docker image fishtownanalytics/dbt:0.18.1.

The operating system you're using:

Using Docker image fishtownanalytics/dbt:0.18.1.

The output of python --version:

Using Docker image fishtownanalytics/dbt:0.18.1.

Additional context

Perhaps the temporary name can be truncated by the length of the suffix to make sure it fits within the limits?

[jtcohen6]

Thanks for opening, @moltar! I agree, this is peskier than it could be.

Following the discussion about this over in #2850, the _real_ max character length of a model name on Postgres is currently 51, because of the appended suffix __dbt_backup in the table and view materializations.

I think we could do as you recommend, and truncate the model name (if >51 characters) to accommodate the suffix. I view that as a reasonable next step on top of the work in #2850, which handles the truncation of _uniquely_ suffixed identifiers in the incremental materialization.

[moltar]

Thank you.

I see this tagged as redshift/pg.

I'd like to note that Redshift names seem to allow names of 127 bytes long.

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

[marinto]

With snapshots the suffix is even longer, e.g. dbt_tmp20201206072656412865. If you add _snapshot to the relation name, not much characters are left to use for the relation name.
Please also consider snapshots when addressing this issue. Thanks:)

[jtcohen6]

Heard @marinto. Like the incremental materialization, the snapshot materialization already uses make_temp_relation, which was addressed in #2850. The question in this issue is how to best cross-apply a similar improvement to the view + table materializations, which do not use make_temp_relation.

[marinto]

@jtcohen6 ah I see, I wasn't aware of #2850. This is exactly what I was looking for 👍