Skip to content
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

get_relations_by_pattern no longer works with foreign data wrappers in postgres #357

Closed
1 of 5 tasks
eculicny opened this issue Apr 28, 2021 · 3 comments
Closed
1 of 5 tasks
Labels
bug Something isn't working cross-db Tooling to provide consistent behaviour across multiple databases help wanted

Comments

@eculicny
Copy link

Describe the bug

While upgrading to 0.6.4 from 0.6.3, we hit an issue that is similar to #350 but for a postgres target that had foreign data wrappers set up while using get_relations_by_pattern

Steps to reproduce

Setup an imported schema extdb_extschema in postgres target database with table that matches '%_'+table_suffix.

Reference in model to get_relations_by_pattern:

{% set relations = dbt_utils.get_relations_by_pattern(schema_pattern='extdb__extschema',
                                                      table_pattern='%_'+table_suffix,
                                                      database=this.database) %}

Run dbt compile on model for target database

Expected results

The successfully retrieves the set of relations to iterate over creating a single model out of the records in the matching tables (e.g. union all of matching relations).

This is the current result when running dbt utils 0.6.3

Actual results

Unhandled runtime errors occur during the get_relations_by_pattern call causing the model to not build.

Screenshots and log output

Unhandled error while executing model.test_model.consolidation
Field "type" of type typing.Union[dbt.contracts.relation.RelationType, NoneType] in dbt.adapters.postgres.relation.PostgresRelation has invalid value 'foreign'

System information

The contents of your packages.yml file:

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.4

dbt version: 0.19.1

Which database are you using dbt with?

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

The output of dbt --version:

installed version: 0.19.1
   latest version: 0.19.1

Up to date!

Plugins:
  - snowflake: 0.19.1
  - redshift: 0.19.1
  - bigquery: 0.19.1
  - postgres: 0.19.1

Additional context

The addition of table_type into the default__get_tables_by_pattern_sql macro causes problems with foreign tables in postgres since they have a type of FOREIGN in information_schema, but dbt doesn't support that type by that name.

Are you interested in contributing the fix?

Yeah. I'm not sure if there are any caveats about what the external type means in dbt core or weird downstream effects of it, but a similar fix to #351 should solve the problem unless there are concerns about ramifications in other adapters.

@eculicny eculicny added bug Something isn't working triage labels Apr 28, 2021
@Aesthet
Copy link
Contributor

Aesthet commented Nov 24, 2021

Any updates on this issue?

@joellabes
Copy link
Contributor

@eculicny @Aesthet sorry for the radio silence here!

I'm not familiar with Postgres unfortunately so don't have anything useful to add here, but it does sound very similar to #350/#351. If one of you wanted to open a PR to resolve it that sounds fine by me.

@jtcohen6 does this sound right to you? Could I prevail upon you to review any PR that comes out of this? I see from the last round there wasn't any practical way to do automated testing, does that still hold?

@joellabes joellabes added cross-db Tooling to provide consistent behaviour across multiple databases help wanted and removed triage labels Nov 24, 2021
@dbeatty10
Copy link
Contributor

Resolved by #476

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working cross-db Tooling to provide consistent behaviour across multiple databases help wanted
Projects
None yet
Development

No branches or pull requests

4 participants