jwills / buenavista Goto Github PK

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?

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)"

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.

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

• grafana/grafana-oss Grafana docker container
• v1.0.4 Grafana TrinoDB Plugin (v1.0.5 is latest atm. and has some bug causing it not to send the query forward actually)
• No auth, no TLS
• Grafana query: SELECT 1 as value;, like in the data source exploration window
• Buenavista HTTP response JSON as below

{"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.

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

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

Copying duckdb/duckdb#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 🤔🦆

Would be great to see this published to PyPi for ease of use!

Now that duckdb supports ADBC do you think the buenavista proxy could also support a wire protocol for it?

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.

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]', '')