Error for new connections

[imaurer]

Thanks for this project. I am running into an issue and I am unsure if it is my fault.

Trying to access parquet files via duckdb and for each new connection, I have to re-run duckdb_execute to get the query to work.

Here is my setup:

CREATE EXTENSION duckdb_fdw;

CREATE SERVER DuckDB_server
FOREIGN DATA WRAPPER duckdb_fdw;

SELECT duckdb_execute(
    'duckdb_server',
    'create or replace view markers as select * from parquet_scan(''/var/annotations/samples/*.parquet'');'
);

CREATE SCHEMA parquet;

IMPORT FOREIGN SCHEMA public
             limit to (markers) 
          FROM SERVER duckdb_server
                 INTO parquet;

select * from parquet.markers limit 100;

This last query works great. However, on creating a new connection, the final SELECT statement fails.

ERROR:  SQL error during prepare: Catalog Error: Table with name markers does not exist!
Did you mean "duckdb_views"?
LINE 1: ... "is_phased", "is_het", "quality" FROM main."markers"
^ SELECT "hgvs_g", "total_depth", "alt_depth", "ref_depth", "vaf", "vaf_alt", "sample_name", "is_phased", "is_het", "quality" FROM main."markers".

I notice the schema says main in the error message. If I run the following in a NEW connection, it works great:

SELECT duckdb_execute(
    'duckdb_server',
    'create or replace view markers as select * from parquet_scan(''/var/annotations/samples/*.parquet'');'
);

select * from parquet.markers limit 100;

Using Postgres 14 in an Alpine Docker instance. Let me know if there are any other details required.

Thanks in advance, Ian

[alitrack]

CREATE SERVER DuckDB_server
FOREIGN DATA WRAPPER duckdb_fdw;

means you create a duckdb in memory, so it will lost after disconnect.

[imaurer]

Thanks for the insight. Going to switch to the parquet_fdw for my use case. I appreciate the response!

[alitrack]

@imaurer but you can give a database name, for example,

CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/tmp/test.db');

then when connect next time, it will keep

[imaurer]

Thanks for the clarification!