no output and no errors

[tobwen]

summary

I tried to access one of my duckdb-databases from within PostgreSQL. Rights are fine, I didn't get any error - but also no functionality. What am I doing wrong?

my database

$ chmod 777 /tmp/mydb
$ duckdb /tmp/mydb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
D SELECT count(*) FROM mydata;
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│     72749940 │
└──────────────┘

inside postgresql

CREATE EXTENSION duckdb_fdw;

SELECT duckdb_fdw_version();
┌────────────────────┐
│ duckdb_fdw_version │
├────────────────────┤
│              20101 │
└────────────────────┘
(1 row)


CREATE SERVER myduckdb_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS ( database '/tmp/mydb' );
CREATE SCHEMA myduckdb;
IMPORT FOREIGN SCHEMA myduckdb FROM SERVER myduckdb_server INTO myduckdb;

-- has no tables
SELECT table_name FROM information_schema.tables WHERE table_schema = 'myduckdb';

SELECT * FROM information_schema.foreign_tables;
┌───────────────────────┬──────────────────────┬────────────────────┬────────────────────────┬─────────────────────┐
│ foreign_table_catalog │ foreign_table_schema │ foreign_table_name │ foreign_server_catalog │ foreign_server_name │
├───────────────────────┼──────────────────────┼────────────────────┼────────────────────────┼─────────────────────┤
└───────────────────────┴──────────────────────┴────────────────────┴────────────────────────┴─────────────────────┘
(0 rows)

-- connection is fine
SELECT duckdb_execute('myduckdb_server', 'SELECT count(*) FROM mydata;');
┌────────────────┐
│ duckdb_execute │
├────────────────┤
│                │
└────────────────┘
(1 row)

my install chain

git clone --depth=1 https://github.com/alitrack/duckdb_fdw
cd duckdb_fdw/

git remote add pr-author https://github.com/alitrack/duckdb_fdw.git
git fetch pr-author pull/38/head:pr-38
git checkout pr-38

wget https://github.com/duckdb/duckdb/releases/download/v0.10.1/libduckdb-linux-amd64.zip
unzip libduckdb-linux-amd64.zip
cp -a libduckdb.so "$(pg_config --libdir)"

# rm -rf /usr/lib/postgresql/16/lib/bitcode/duckdb_fdw/
# USE_PGXS=1 make uninstall
# USE_PGXS=1 make distclean
USE_PGXS=1 make
USE_PGXS=1 make install

[alitrack]

IMPORT FOREIGN SCHEMA myduckdb FROM SERVER myduckdb_server INTO myduckdb;

should be

IMPORT FOREIGN SCHEMA public FROM SERVER myduckdb_server INTO myduckdb;

or make sure you have myduckdb schema on your duckdb and has table in it.

[tobwen]

IMPORT FOREIGN SCHEMA public FROM SERVER myduckdb_server INTO myduckdb;

Oh dear... Now I feel ashamed. Thanks for the hint.

Funny thing: The exact same query using duckdb_fdw is 100x faster than PostgreSQL-native. That's crazy.