Make pg/redshift database verification case-insensitive

[rpammeraal]

Database names are case sensitive in Postgres (and other databases).
When running DBT I got the following:
ERROR: Cross-db references not allowed in postgres (stg_rammeraal_82136 vs STG_rammeraal_82136)

To avoid this, please apply the following patch:

index af27aef2..f442281e 100644
--- a/plugins/postgres/dbt/adapters/postgres/impl.py
+++ b/plugins/postgres/dbt/adapters/postgres/impl.py
@@ -23,10 +23,8 @@ class PostgresAdapter(SQLAdapter):
     def verify_database(self, database):
         if database.startswith('"'):
             database = database.strip('"')
-        else:
-            database = database.lower()
         expected = self.config.credentials.database
-        if database != expected:
+        if database.lower() != expected.lower():
             raise dbt.exceptions.NotImplementedException(
                 'Cross-db references not allowed in {} ({} vs {})'
                 .format(self.type(), database, expected)```

This may be an issue with other database vendors as well.

[drewbanin]

Thanks for the report @rpammeraal! Please feel free to submit a pull request for this change -- it's a very good one :)

We've recently added a CLA to the dbt contribution process. This CLA ensures that contributions to the dbt codebase are "original work that does not violate any third-party license agreement." As such, we're not well-suited to copy/paste code into patches to the dbt-core repository. If you're able to submit a PR for this, I'd be very happy to merge it! If not, we can retitle this issue to "Make Postgres database comparisons case-insensitive" and prioritize it on our own accord. Let us know!

[rpammeraal]

I looked at requirements on how to create a PR -- it is quite a bit of work. Also, some other changes need to be made in the underlying postgres modules to ensure that database (and other entity) names are always compared in lower case in postgres -- however, I don't have the bandwidth to set up, debug and test all this. Thanks, Roy

…

On Mon, Oct 7, 2019 at 6:22 PM Drew Banin ***@***.***> wrote: Thanks for the repot @rpammeraal <https://github.com/rpammeraal>! Please feel free to submit a pull request for this change -- it's a very good one :) We've recently added a CLA <https://docs.getdbt.com/docs/contributor-license-agreements> to the dbt contribution process. This CLA ensures that contributions to the dbt codebase are "original work that does not violate any third-party license agreement." As such, we're not well-suited to copy/paste code into patches to the dbt-core repository. If you're able to submit a PR for this, I'd be very happy to merge it! If not, we can retitle this issue to "Make Postgres database comparisons case-insensitive" and prioritize it on our own accord. Let us know! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1800>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AGE35HAZ2MSEY2UWCYOVHITQNPOEFANCNFSM4I43HUTQ> .

[drewbanin]

I don't believe the fix in #1918 fully addresses this issue. While the cross-db error message has indeed been addressed, dbt now fails with an error like:

Compilation Error in model debug2 (models/debug2.sql)
  When searching for a relation, dbt found an approximate match. Instead of guessing
  which relation to use, dbt will move on. Please delete "mydb"."test_schema"."debug2", or rename it to be less ambiguous.
  Searched for: "MyDB"."test_schema"."debug2"
  Found: "mydb"."test_schema"."debug2"

Here, my database is named MyDB on Postgres. We can reproduce this failure mode with:

-- models/my_model.sql
select 1 as id

And a profile configured with:

debug:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: drew
      pass: password
      port: 5433
      dbname: MyDB
      schema: test_schema
      threads: 8

Followed by:

dbt run
dbt run

The first dbt run will succeed while the second dbt run will fail.