when using DBT along with DuckDB to create tables, I encountered a runtime error during the execution of a model with it's materialization set to table.
we suspected a Postgres parser error folding unquoted identifiers into lower case since it kept searching for the wrong lower-cased relation.
finally I tried to reproduce it in duckdb without having dbt as a factor
Behavior
these are the steps that are done in normal flow
1 - start a transaction
2 - create the new version of the table in a temporary location (suffixed dbt_tmp )
3 - rename the existing version of the table to be the backup version (suffixed __dbt_backup)
4 - rename the new version of the table to remove the suffix (dbt_tmp → ``)
5 - commit the transaction
6 - drop the old/backup version (on many databases, drops need to happen outside of transactions)
but it crashes in the middle returning
Runtime Error in model {model_name} (models_{dir_name}\{model_name}.sql)
Failed to execute query "ALTER TABLE {schema_name}.{capitalized_table_name}__dbt_tmp RENAME TO {capitalized_table_name}":
ERROR: relation "{schema_name}.{lowered_table_name}__dbt_tmp" does not exist
To Reproduce
1 - connect to the postgres database
D attach 'connection_string' as pg;
2 - create a table named Tst and check existence
D create table pg.rca_standardized.Tst(id INT);
D describe pg.rca_standardized.Tst;
3 - try and alter the name to lower case
D alter table pg.rca_standardized.Tst rename to tst;
Error: Invalid Error: Failed to execute query "BEGIN TRANSACTION;
ALTER TABLE rca_standardized.Tst RENAME TO tst": ERROR: relation "rca_standardized.tst" does not exist
already searches for the lower-cased relation
4 - try and alter the table to add a column
D alter table pg.rca_standardized.Tst add column name varchar;
Error: Invalid Error: Failed to execute query "BEGIN TRANSACTION;
ALTER TABLE rca_standardized.Tst ADD COLUMN "name" VARCHAR": ERROR: relation "rca_standardized.tst" does not exist
again searches for the lower-cased relation
5 - tried different ways to double-quote it like
D alter table pg.rca_standardized."Tst" add column "name" varchar;
Error: Invalid Error: Failed to execute query "BEGIN TRANSACTION;
ALTER TABLE rca_standardized.Tst ADD COLUMN "name" VARCHAR": ERROR: relation "rca_standardized.tst" does not exist
OS:
Win11 & Ubuntu 22.04
PostgreSQL Version:
15.6
DuckDB Version:
0.10.0 (20b1486d11)
DuckDB Client:
Python 3.12.0
Full Name:
M. Alam
Affiliation:
nWeave
Have you tried this on the latest main branch?
[X] I agree
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
What happens?
Description
Behavior
these are the steps that are done in normal flow 1 - start a transaction 2 - create the new version of the table in a temporary location (suffixed dbt_tmp ) 3 - rename the existing version of the table to be the backup version (suffixed __dbt_backup) 4 - rename the new version of the table to remove the suffix (dbt_tmp → ``) 5 - commit the transaction 6 - drop the old/backup version (on many databases, drops need to happen outside of transactions)
but it crashes in the middle returning
To Reproduce
1 - connect to the postgres database
2 - create a table named
Tstand check existence3 - try and alter the name to lower case
already searches for the lower-cased relation
4 - try and alter the table to add a column
again searches for the lower-cased relation
5 - tried different ways to double-quote it like
OS:
Win11 & Ubuntu 22.04
PostgreSQL Version:
15.6
DuckDB Version:
0.10.0 (20b1486d11)
DuckDB Client:
Python 3.12.0
Full Name:
M. Alam
Affiliation:
nWeave
Have you tried this on the latest
mainbranch?Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?