jwills / buenavista Goto Github PK
View Code? Open in Web Editor NEWA Postgres Proxy Server in Python
License: Apache License 2.0
A Postgres Proxy Server in Python
License: Apache License 2.0
Copying duckdb/duckdb#11370 per @jwills invitation
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
$ 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
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"
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
Linux
0.10.1
CLI
P.S. I promise not to try to postgres-ify the second duckdb instance that is connected to the first duckdb instance 🤔🦆
I was having an issue with running the buenavista docker image because the "buneavista.backend" module did not exist. Looks like this is because of recent a commit 8d78552 renaming the backend module to backends.
After I changed the CMD in the docker file from python -m buenavista.backend.duckdb
to python -m buenavista.backends.duckdb
everything worked.
I will open up a PR with the fix.
What is the level of Postgres emulation that Buena Vista aims for?
For example, if I do \d
in psql to get list of tables, I get following error:
Catalog Error: Scalar Function with name pg_get_userbyid does not exist!
Did you mean "pg_get_expr"?
LINE 1: ...HEN 'partitioned index' END AS "Type", pg_catalog.PG_GET_USERBYID(c.relowner) ...
That is because the pg_
tables don't exist. DBeaver for example is broken when browsing the database as well, since it relies on them being there:
Would it make sense to add simulation of those tables into Buena Vista or is it out of scope and only the wire protocol is the point?
I'm trying to send regexp queries to a duckdb database via buenavista.
But buenavista removes 'g' flag from the function:
INFO:buenavista.postgres:Input SQL: select regexp_replace('mr .', '[^a-zA-Z]', '', 'g')
INFO:buenavista.postgres:Rewritten SQL: SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '')
grafana/grafana-oss
Grafana docker containerSELECT 1 as value;
, like in the data source exploration window{"id": "1701431284.415317_1701431289766", "infoUri": "http://127.0.0.1/info", "stats": {"state": "COMPLETE", "waitingForPrerequisites": false, "queued": false, "scheduled": false, "nodes": 1, "totalSplits": 1, "queuedSplits": 0, "runningSplits": 0, "completedSplits": 1, "cpuTimeMillis": 0, "wallTimeMillis": 0, "waitingForPrerequisitesTimeMillis": 0, "queuedTimeMillis": 0, "elapsedTimeMillis": 9, "processedRows": 0, "processedBytes": 0, "peakMemoryBytes": 0, "peakTotalMemoryBytes": 0, "peakTaskTotalMemoryBytes": 0, "spilledBytes": 0, "rootStage": null, "runtimeStats": null}, "warnings": null, "partialCancelUri": null, "columns": [{"name": "value", "type": "integer", "typeSignature": {"rawType": "integer", "arguments": []}}], "data": [[1]], "updateType": null, "updateCount": null}
==> Grafana does not seem to understand the response somehow?
The issue: Grafana Query Inspector never finds any rows on the responses for some reason.
Now that duckdb supports ADBC do you think the buenavista proxy could also support a wire protocol for it?
Hi Josh,
Thank you for your project! It helped me.
I found some bug while trying to connect to Buenavista via DBeaver:
INFO:buenavista.postgres:Input SQL: SELECT n.oid,n.,d.description FROM pg_catalog.pg_namespace n
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=n.oid AND d.objsubid=0 AND d.classoid='pg_namespace'::regclass
ORDER BY nspname
INFO:buenavista.postgres:Rewritten SQL: SELECT n.oid, n., d.description FROM pg_catalog.pg_namespace AS n LEFT OUTER JOIN pg_catalog.pg_description AS d ON d.objoid = n.oid AND d.objsubid = 0 AND d.classoid = CAST('pg_namespace' AS REGCLASS) ORDER BY nspname
ERROR:buenavista.postgres:Catalog Error: Type with name REGCLASS does not exist!
After some trials, I found a way:
elif "AND d.classoid = CAST('pg_namespace' AS REGCLASS)" in sql:
sql = sql.replace("AND d.classoid = CAST('pg_namespace' AS REGCLASS)", "")
logger.info("Rewritten SQL to remove REGCLASS reference: " + sql)
Added after the line
buenavista/buenavista/backends/duckdb.py
Line 181 in 37ed325
I don't like how it looks like, but it works!
PS: I would also add to the documentation that connecting to a Buenavista server via the psycopg2 driver is possible (tested). I struggled with the sqlalchemy library, it didn't work
A container (see Dockerfile below) with official python 3.11.1 and buenavista
installed complains with this message when started:
Using in-memory DuckDB database
Traceback (most recent call last):
File "/src/buenavista/examples/duckdb_server.py", line 202, in <module>
db.execute(
duckdb.CatalogException: Catalog Error: Cannot drop entry "pg_database" because it is an internal system entry
This issue can be replicated with this Dockerfile and running docker build -t buenavista . && docker run --rm buenavista
:
# syntax=docker/dockerfile:1.4
FROM docker.io/python:3.11.1
ENV TZ=Europe/Stockholm
ENV DEBIAN_FRONTEND=noninteractive
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone
RUN apt-get update -y && apt-get install -y --no-install-recommends \
git
WORKDIR /src
# these seem to be requirements
RUN pip install duckdb pyarrow
RUN git clone --depth=1 https://github.com/jwills/buenavista.git
WORKDIR /src/buenavista
CMD PYTHONPATH=. python3 examples/duckdb_server.py
When I create a new database connection in dbeaver, I receive an error message:
(base) C:\Users\thinkpad>python -m buenavista.examples.duckdb_postgres D:\abc.db
Using DuckDB database at D:\abc.db
Listening on 0.0.0.0:5433
Catalog Error: Type with name REGCLASS does not exist!
Did you mean "real"?
Catalog Error: Table Function with name pg_get_keywords does not exist!
Did you mean "main.duckdb_keywords"?
LINE 1: SELECT GROUP_CONCAT(word, ',') FROM pg_catalog.PG_GET_KEYWORDS() WHERE word...
^
Catalog Error: Type with name REGCLASS does not exist!
Did you mean "real"?
Please explain why this is and how it needs to be resolved? Thank you.
When I try connnect to Power BI, I haved to provide username, password, then I gave it admin:admin, but Power Bi refused with this message: Unable to connect. We encounted an error while trying to connect. Detailed: "PostgresSQL: No password has been provided but the backend requires one (in SASL/SCRAM-SHA-256)"
Would be great to see this published to PyPi for ease of use!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.