search

duckdb / postgres_scanner

MIT License
195 stars 35 forks source link

Cannot Render Schema on AWS/RDS #143

Closed ericandrewschmidt closed 7 months ago

ericandrewschmidt commented 7 months ago

I am seeking to connect DuckDB to a Postgres 15.3 Aurora cluster using the Postgres scanner extension.

I can attach to Postgres using the following code; however, when I SHOW tables, there are none in the DuckDB catalog. import duckdb duckdb.execute('INSTALL postgres;') duckdb.execute('LOAD postgres;') duckdb.execute('CALL postgres_attach("dbname=** \ user=** password=** \ host=** \ port=** connect_timeout=** application_name=**\ ");') duckdb.sql('USE main') duckdb.sql('SHOW TABLES').show()

│ name │ │ varchar │ ├─────────┤ │ 0 rows │

I can confirm that DuckDb is connecting and making calls, specifically querying for the tables names (from the Postgress session logs):

SELECT relname FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid JOIN pg_attribute ON pg_class.oid = pg_attribute.attrelid WHERE relkind = 'r' AND attnum > 0 AND nspname = 'public' GROUP BY relname ORDER BY relname;

I can confirm that if I run this query from the DBeaver or command line, that schema/table info is returned. There are over 200 tables in the database.

What would prevent me from being able to see the tables in DuckDb with SHOW TABLES?

Also, I tried using the single table scan function, but that did not work either.

duckdb.sql('SELECT * FROM postgres_scan("dbname=** \ host=** \ user=** password=** port=** ", "**", "**") LIMIT 10;').show()

Failed to execute query "SELECT pg_is_in_recovery(), pg_export_snapshot(), (select count(*) from pg_stat_wal_receiver)": ERROR: Function pg_stat_get_wal_receiver() is currently not supported in Aurora.

Do you have any tips on how to get this working?

Thanks

Mytherin commented 7 months ago

This looks like a duplicate of #129, try the latest nightly build:

force install postgres_scanner from 'http://nightly-extensions.duckdb.org';
ericandrewschmidt commented 7 months ago

That works! Thank you.

On Tue, Dec 5, 2023 at 7:40 AM Mark @.***> wrote:

This looks like a duplicate of #129 https://github.com/duckdb/postgres_scanner/issues/129, try the latest nightly build:

force install postgres_scanner from 'http://nightly-extensions.duckdb.org';

— Reply to this email directly, view it on GitHub https://github.com/duckdb/postgres_scanner/issues/143#issuecomment-1841047385, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACOF4RJKM2CIBVONGFG42E3YH46ABAVCNFSM6AAAAABAH3QUQCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBRGA2DOMZYGU . You are receiving this because you authored the thread.Message ID: @.***>

-- Cell: 425.941.6009 Web: djrhythma.com http://www.djrhythma.com Insta: @djrhythma https://www.instagram.com/djrhythma/ Mixes: mixcloud.com/djrhythma https://www.mixcloud.com/djrhythma/