-
Notifications
You must be signed in to change notification settings - Fork 1.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Ability to set search_path in Postgresql #1477
Comments
We actually discussed this issue at #1476. P.S: I'm not trying to be sarcastic. Again, sorry for the misunderstanding. 😬 |
Hi @buremba - thanks for making this issue. Let me outline some of the basics on how dbt works, and how the connection configs differ between Snowflake and Postgres/Redshift:
The create table "schema_name"."table_name" as (
select ...
); The
This notion of repeatable builds in configurable schemas is super core to the dbt workflow. We've written about managing environments in the dbt documentation. So, the
Postgres and Redshift both support search paths. These search paths are used by the database to resolve a schemaless relation name. You provided this example above:
You're right to identify that on Postgres/Redshift, the database would go looking for a table called
If a dbt user on Redshift has a model with the following code:
Then this code will be interpreted by Redshift as:
If we changed the
This breaks the project.
dbt is intended to be used with different (dev/prod) environments. Moreover, it's assumed that source data lives in different schemas than the dbt models are built into. If a user is selecting from a relation in a model without specifying a schema, then that means they're selecting from other dbt models, right? These users should absolutely be using the Do I have the right idea here? This is the most confusing thing to me about the challenges that you're describing.
I would merge a PR that did the following things:
|
Thanks for taking the time to clarify the things @drewbanin! My understanding was that the credentials in the profile section are for the It may be because I started using dbt with Snowflake and used the When I switched to Postgresql from Snowflake, I followed the same procedure and expected the same outcome. I still believe that dbt should be consistent across the databases but it looks like the main cause of the difference is that both PG and Redshift has a default Do you want me to create a PR that uses |
Thanks for the update @buremba - this all makes a lot of sense! Feel free to re-open that PR (or I can do it for you if you're unable) to contribute this functionality to dbt. My opinion is that the config should be called
I think calling it |
That sounds right, thanks for being so responsive! |
Fixed in #1476 |
Issue
Issue description
The
schema
parameter in Postgresql credentials is not being used.Results
I was expecting dbt to pass the schema to Postgresql similar to Snowflake and BigQuery. If that's not something that you want, I believe that we should remove that parameter from
PostgresqlCredentials
.Also, please note that if this parameter is not set in credentials, we won't be overriding the schema and it will default to the user's
search_path
. If that's not set, the default ispublic
.System information
The output of
dbt --version
:The operating system you're running on: OS X
The python version you're using: Python 3.6.4
Steps to reproduce
We use both Snowflake and Postgresql and use the
schema
parameter in both adapters. When we execute a simple query such asSELECT count(*) my_table
in Snowflake, dbt automatically execute the query in the schema that I defined since it passes schema to Snowflake.However; that's not the case in Postgresql,
SELECT count(*) my_table
references public.my_table no matter what I use inschema
in my credentials. I believe that's confusing to the analysts because it's not consistent across the databases.The text was updated successfully, but these errors were encountered: