dbt-duckdb issue with Buena Vista remote and using postgres_scan in model

[grahamwetzler]

I'm getting this error when trying to build a model that uses postgres_scan in dbt-duckdb and using Buena Vista as a remote:

15:33:27  Runtime Error in model stg_ltss (models/staging/stg_ltss.sql)
15:33:27    Catalog Error: Function with name "postgres_scan" is not in the catalog, but it exists in the postgres_scanner extension.
15:33:27    
15:33:27    To install and load the extension, run:
15:33:27    INSTALL postgres_scanner;
15:33:27    LOAD postgres_scanner;

Steps to reproduce:

• Run Buena Vista with python3 -m buenavista.examples.duckdb_postgres target/dbt.duckdb

• Configure profiles.yml:

default:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ./target/dbt.duckdb
      extensions:
        - postgres
      remote:
        host: 0.0.0.0
        port: 5433
        user: postgres

• Materialize a model that uses postgres_scan to connect to another Postgres server

[grahamwetzler]

A workaround I found is to add LOAD postgres_scanner to the model's pre-hook or the project's on-run-start

[jwills]

Hey @grahamwetzler! I'm on vacation (I go on vacation a lot these days) but the intent is to run a BV server that is dbt-aware via the duckdbt server (which essentially configures a BV server by using the config info from the profiles.yml file, including e.g. loading extensions and what not.)

I still need to update it for dbt-duckdb 1.6.0 and do some other things (see above re: vacation), but it's on my TODO list for when I get back!

[grahamwetzler]

Ah, ok. So user error? If so I will close and wait patiently for your update 😀

[jwills]

Nah, more like laziness and poor documentation on my part. ;-)