Connecting to buenavista duckdb/postgres instance from DuckDB

liquidcarbon commented 2 months ago

Copying https://github.com/duckdb/duckdb/issues/11370 per @jwills invitation

What happens?

Trying to make local duckdb mimic postgres via buenavista. Seems like you should be able to connect to that from another machine or even the same machine using DuckDB Postgres adapter.

It does not work (see below).

I'm not sure if this is the an issue with DuckDB Postgres or with buenavista thus cc @jwills

To Reproduce

Buenavista:


$ pip list | egrep "buenavista|duckdb|starlette"
buenavista                0.4.0
duckdb                    0.10.1
starlette                 0.36.3

$ python -m buenavista.examples.duckdb_postgres
Using in-memory DuckDB database
Listening on 0.0.0.0:5433
Catalog Error: unrecognized configuration parameter "server_version"

Did you mean: "user"
Catalog Error: unrecognized configuration parameter "server_version"

Did you mean: "user"

DuckDB from the same box

$ ./duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D ATTACH 'dbname=memory host=localhost port=5433' AS db (TYPE postgres, READ_ONLY);
Invalid Error: Failed to execute query "SELECT CURRENT_SETTING('server_version'), (SELECT COUNT(*) FROM pg_settings WHERE name LIKE 'rds%')": Catalog Error: unrecognized configuration parameter "server_version"

Did you mean: "user"

D ATTACH 'host=localhost port=5433' AS db (TYPE postgres, READ_ONLY);
Invalid Error: Failed to execute query "SELECT CURRENT_SETTING('server_version'), (SELECT COUNT(*) FROM pg_settings WHERE name LIKE 'rds%')": Catalog Error: unrecognized configuration parameter "server_version"

Did you mean: "user"

D ATTACH 'host=0.0.0.0 port=5433' AS db (TYPE postgres, READ_ONLY);
Invalid Error: Failed to execute query "SELECT CURRENT_SETTING('server_version'), (SELECT COUNT(*) FROM pg_settings WHERE name LIKE 'rds%')": Catalog Error: unrecognized configuration parameter "server_version"

Did you mean: "user"

DuckDB from remote box:

IO Error: Unable to connect to Postgres at dbname=memory host=1.2.3.4 port=5433: connection to server at "1.2.3.4", port 5433 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request

OS:

Linux

DuckDB Version:

0.10.1

DuckDB Client:

CLI

P.S. I promise not to try to postgres-ify the second duckdb instance that is connected to the first duckdb instance 🤔🦆

liquidcarbon commented 1 month ago

ping?

jwills commented 1 month ago

Oh sorry I thought I posted somewhere that this was me-- I don't make an effort to implement the binary Postgres protocol in the way that DuckDB itself needs, so I'm not going to be able to support this use case anytime in the near future.

jwills / buenavista