对postgres wire协议服务端buenavista的简单测试

原创已于 2025-10-15 09:27:21 修改 · 490 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#python #duckdb #postgresql #协议

于 2025-10-15 09:20:50 首次发布

编程语言软件工程同时被 2 个专栏收录

267 篇文章

订阅专栏

数据库

178 篇文章

订阅专栏

张泽鹏先生给我推荐 https://github.com/jwills/buenavista，作者是另一个duckdb官方项目dbt-duckdb的作者之一，想必功能比一般的第三方要齐全吧。

buenavista安装有2种方式，第1是利用docker镜像。可惜没有提供arm64版本。

sudo docker pull ghcr.io/jwills/buenavista:1.0.35
1.0.35: Pulling from jwills/buenavista
no matching manifest for linux/arm64/v8 in the manifest list entries

第2是通过pip安装，依赖项还挺多的

python3 pip.pyz install buenavista --break-system-packages
Collecting buenavista
  Downloading buenavista-0.5.0-py3-none-any.whl.metadata (2.2 kB)
Collecting fastapi<1.0.0,>=0.109.0 (from buenavista)
  Downloading fastapi-0.119.0-py3-none-any.whl.metadata (28 kB)
Collecting pydantic<3,>=2 (from buenavista)
  Downloading pydantic-2.12.2-py3-none-any.whl.metadata (85 kB)
Requirement already satisfied: sqlglot in /usr/local/lib/python3.11/dist-packages (from buenavista) (27.4.1)
Collecting starlette<0.49.0,>=0.40.0 (from fastapi<1.0.0,>=0.109.0->buenavista)
  Downloading starlette-0.48.0-py3-none-any.whl.metadata (6.3 kB)
Requirement already satisfied: typing-extensions>=4.8.0 in /usr/local/lib/python3.11/dist-packages (from fastapi<1.0.0,>=0.109.0->buenavista) (4.14.1)
Collecting annotated-types>=0.6.0 (from pydantic<3,>=2->buenavista)
  Downloading annotated_types-0.7.0-py3-none-any.whl.metadata (15 kB)
Collecting pydantic-core==2.41.4 (from pydantic<3,>=2->buenavista)
  Downloading pydantic_core-2.41.4-cp311-cp311-manylinux_2_17_aarch64.manylinux2014_aarch64.whl.metadata (7.3 kB)
Collecting typing-inspection>=0.4.2 (from pydantic<3,>=2->buenavista)
  Downloading typing_inspection-0.4.2-py3-none-any.whl.metadata (2.6 kB)
Collecting anyio<5,>=3.6.2 (from starlette<0.49.0,>=0.40.0->fastapi<1.0.0,>=0.109.0->buenavista)
  Downloading anyio-4.11.0-py3-none-any.whl.metadata (4.1 kB)
Requirement already satisfied: idna>=2.8 in /usr/local/lib/python3.11/dist-packages (from anyio<5,>=3.6.2->starlette<0.49.0,>=0.40.0->fastapi<1.0.0,>=0.109.0->buenavista) (3.10)
Collecting sniffio>=1.1 (from anyio<5,>=3.6.2->starlette<0.49.0,>=0.40.0->fastapi<1.0.0,>=0.109.0->buenavista)
  Downloading sniffio-1.3.1-py3-none-any.whl.metadata (3.9 kB)
Downloading buenavista-0.5.0-py3-none-any.whl (27 kB)
Downloading fastapi-0.119.0-py3-none-any.whl (107 kB)
Downloading pydantic-2.12.2-py3-none-any.whl (460 kB)
Downloading pydantic_core-2.41.4-cp311-cp311-manylinux_2_17_aarch64.manylinux2014_aarch64.whl (2.0 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 2.0/2.0 MB 15.7 kB/s eta 0:00:00
Downloading starlette-0.48.0-py3-none-any.whl (73 kB)
Downloading anyio-4.11.0-py3-none-any.whl (109 kB)
Downloading annotated_types-0.7.0-py3-none-any.whl (13 kB)
Downloading sniffio-1.3.1-py3-none-any.whl (10 kB)
Downloading typing_inspection-0.4.2-py3-none-any.whl (14 kB)
Installing collected packages: typing-inspection, sniffio, pydantic-core, annotated-types, pydantic, anyio, starlette, fastapi, buenavista
Successfully installed annotated-types-0.7.0 anyio-4.11.0 buenavista-0.5.0 fastapi-0.119.0 pydantic-2.12.2 pydantic-core-2.41.4 sniffio-1.3.1 starlette-0.48.0 typing-inspection-0.4.2
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager, possibly rendering your system unusable. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv. Use the --root-user-action option if you know what you are doing and want to suppress this warning.

运行buenavista自带的duckdb服务器例子

root@66d4e20ec1d7:/par# python3 -m buenavista.examples.duckdb_postgres buen.db
Using DuckDB database at buen.db
Listening on 0.0.0.0:5433

另开一个终端，用之前用过的pgproto客户端连接。
事先编写一个测试脚本pgpo3.txt如下

#
# Test data example
#
'Q'	"create table foods as SELECT * FROM '/par/foods.csv' where category!='meat'"
'Y'
'Q'	"SELECT category,sum(fats_g) FROM foods where sugars_g<1 group by category"
'Y'
'Q'	"pivot foods on fats_g USING sum(sugars_g)"
'Y'

执行命令行

/par/pgproto-master/src# ./pgproto -h 127.0.0.1 -p 5433 -f /par/pgpo3.txt
FE=> Query (query="create table foods as SELECT * FROM '/par/foods.csv' where category!='meat'")
<= BE ErrorResponse(M Catalog Error: Table with name "foods" already exists! )
<= BE ReadyForQuery(I)
FE=> Query (query="SELECT category,sum(fats_g) FROM foods where sugars_g<1 group by category")
<= BE RowDescription
字段数量: 2
字段 1: category
字段 2: sum(fats_g)
<= BE DataRow
seafood|26.5
<= BE DataRow
fruit|4.5
<= BE CommandComplete(SELECT 2)
<= BE ReadyForQuery(I)
FE=> Query (query="pivot foods on fats_g USING sum(sugars_g)")
<= BE RowDescription
字段数量: 9
字段 1: category
字段 2: calories
字段 3: 0.0
字段 4: 0.5
字段 5: 1.5
字段 6: 10.0
字段 7: 4.5
字段 8: 5.0
字段 9: 7.0
<= BE DataRow
seafood|140|����������������

输出前两个查询结果没有问题，第三个查询pivot foods on fats_g USING sum(sugars_g)语句执行了，但是取数有问题。服务端也报错了

ConnectionResetError: [Errno 104] Connection reset by peer

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.11/socketserver.py", line 691, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.11/socketserver.py", line 361, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.11/socketserver.py", line 755, in __init__
    self.handle()
  File "/usr/local/lib/python3.11/dist-packages/buenavista/postgres.py", line 352, in handle
    self.send_error(e)
  File "/usr/local/lib/python3.11/dist-packages/buenavista/postgres.py", line 661, in send_error
    self.wfile.write(err_sig + out)
  File "/usr/lib/python3.11/socketserver.py", line 834, in write
    self._sock.sendall(b)
BrokenPipeError: [Errno 32] Broken pipe

单独编写pgpo4.txt

'Q' "select list(i) from range(3)t(i)"
'Y'

也得不出结果，

./pgproto -h 127.0.0.1 -p 5433 -f /par/pgpo4.txt
FE=> Query (query="select list(i) from range(3)t(i)")
<= BE RowDescription
字段数量: 1
字段 1: list(i)
<= BE ErrorResponse(M sequence item 0: expected str instance, int found )
read_it: EOF detected

服务端报错

sequence item 0: expected str instance, int found
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/buenavista/postgres.py", line 332, in handle
    self.handle_query(ctx, payload)
  File "/usr/local/lib/python3.11/dist-packages/buenavista/postgres.py", line 444, in handle_query
    row_count = self.send_data_rows(query_result)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/buenavista/postgres.py", line 634, in send_data_rows
    v = converter(r)
        ^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/buenavista/postgres.py", line 136, in <lambda>
    BVType.INTEGERARRAY: (1007, lambda v: "{" + ",".join(v) + "}"),
                                                ^^^^^^^^^^^
TypeError: sequence item 0: expected str instance, int found
sequence item 0: expected str instance, int found

如果写成select list(i::varchar) from range(3)t(i)则可以取得结果。

FE=> Query (query="select list(i::varchar) from range(3)t(i)")
<= BE RowDescription
字段数量: 1
字段 1: list(CAST(i AS VARCHAR))
<= BE DataRow
{0,1,2}
<= BE CommandComplete(SELECT 1)
<= BE ReadyForQuery(I)

看来buenavista还不支持数字列表。