-
Notifications
You must be signed in to change notification settings - Fork 246
Apache Cloudberry through ODBC into Apache Arrow (adbcBridge 0.1.0) — is multi-argument unnest a safe bulk-ingest path on Cloudberry? #1939
adbcBridge is a small Apache-2.0 ADBC driver written in C11 that loads an ODBC driver and exposes it through the Arrow ADBC C ABI: block-cursor reads straight into Arrow record batches, bulk ingest, metadata, partitioned parallel reads. I ran Apache Cloudberry through it as one of 46 databases in a single compatibility workload, and it is one of the few entries that needed no driver quirks and no tolerance flags at all — so I wanted to share the entry here and ask one question at the end.
What was verified (Apache Cloudberry 2.1.0-incubating, woblerr/cloudberry image; driver psqlodbc 16, PostgreSQL wire):
- Linux: PASS
- macOS arm64: PASS (amd64 emulated)
- Windows x64: PASS (compose service unchanged, 3 GB / shm 1 GB)
What the compatibility entry records: an MPP cluster of PostgreSQL 14 segments behind one coordinator, driven by the postgres entry's types unchanged (INTEGER, DOUBLE PRECISION, VARCHAR, BYTEA, DATE, TIMESTAMP, NUMERIC(10,3), BOOLEAN all behave as on stock PostgreSQL) and, unlike CockroachDB, needing no PRIMARY KEY. Since it reports SQL_DBMS_NAME "PostgreSQL" behind the same psqlodbcw.so, no driver-name quirk could be correct here without also firing on real PostgreSQL; the only place Cloudberry is named in the bridge is a fork test on the version() banner. Extra steps cover what the standard workload cannot tell apart from PostgreSQL: a DISTRIBUTED BY table whose bulk-ingested rows occupy both segments plus an aggregate merged on the coordinator (Gather Motion 2:1, GPORCA), and append-optimized column-oriented storage read from pg_am as ao_column.
Full entry: https://github.com/singhpratech/adbcbridge/blob/main/docs/COMPATIBILITY.md
The question. For real PostgreSQL the bridge ingests through a multi-argument unnest form — INSERT INTO t SELECT * FROM unnest(1ドル::bigint[], 2ドル::text[], ...) with one array parameter per column — and it only turns that on when version() is a PostgreSQL banner carrying no fork marker, so Cloudberry (banner PostgreSQL 14.4 (Apache Cloudberry 2.1.0-incubating ...)) keeps the plain multi-row INSERT path. Probing Cloudberry directly, the unnest form works exactly as on PostgreSQL — from array literals and from bound bigint[]/text[] parameters, into heap, append-optimized row and append-optimized column tables, across the segments — and server-side it lands 5,000 rows roughly an order of magnitude faster than the multi-row INSERT it keeps (~530k vs ~33k rows/s, bare SQL on a shared host).
Is relying on multi-argument unnest expansion something Cloudberry considers stable across releases, the same way PostgreSQL does — i.e. is it reasonable to let Cloudberry through that gate? If there is a caveat (GPORCA vs the planner, distribution of the unnested rows, anything on the 2.x roadmap), I would rather know before flipping it.
Trying it (Python; Rust, Go, Java and C# are on the docs site):
pip install adbcbridge
import adbcbridge with adbcbridge.connect(uri="Driver=psqlodbcw.so;Servername=127.0.0.1;Port=5432;Database=mydb;Username=gpadmin;Password=...;") as conn: with conn.cursor() as cur: cur.execute("SELECT ...") table = cur.fetch_arrow_table() # a pyarrow.Table
Links: repository https://github.com/singhpratech/adbcbridge · docs https://adbcbridge.org/docs/ · upstream notes https://github.com/singhpratech/adbcbridge/blob/main/docs/UPSTREAM.md · PyPI https://pypi.org/project/adbcbridge/
It is a 0.1.0. If the entry says something wrong about Cloudberry, or you run a version I didn't, an issue on the repository with the details is the most useful thing you could send.
All reactions
-
👍 1
Replies: 1 comment 1 reply
The probe shows that the PostgreSQL-compatible path works today, but I would not make the banner the compatibility contract. Enable it through a capability test run in a rolled-back transaction, and retain the multi-row fallback. The probe should include unequal-length arrays because PostgreSQL pads shorter arrays with NULL, plus a distributed table and the AO storage modes you already test. That protects the bridge if planner or fork behavior changes without changing the version banner.
PostgreSQL documents multi-array UNNEST here: https://www.postgresql.org/docs/14/functions-array.html. Until Cloudberry documents an equivalent stability guarantee, capability detection is safer than a permanent fork allow-list.
All reactions
Thank you, this is exactly the kind of answer I was hoping for, and I agree with the principle: a capability test is a better contract than a banner.
For completeness, here is what the bridge does today. There is already a capability probe, and it is what actually protects the fast path: on a connection's first bulk ingest it runs one SELECT ... FROM unnest('{1,2,3,4,5}'::bigint[], '{"a,b}","",NULL,"x\"y","p\\q"}'::text[]) AS t(a, b) and compares the aggregated answer to the string PostgreSQL produces, which pins down positional pairing of the two arrays, a NULL element, an empty element, a separator and a brace inside a quoted element, an escaped quote and an escaped backslash. Any other answer, or an error, and the connection keeps the multi-row INSERT path for good; the fallback you describe is always there. The probe is a plain read with no side effects, so it needs no transaction to roll back.
What the banner does is sit in front of that probe: a fork marker in version() means the probe is never even asked. So the change you are proposing is smaller than it sounds. Drop the fork allow-list, let the probe decide for any server behind psqlodbc, keep the fallback. Your unequal-length case is a good addition to the probe. The bridge only ever sends arrays of equal length, one per column, but a server that does not pad the shorter array with NULL the way PostgreSQL does is a server whose unnest semantics differ, and the probe should say so rather than assume. The distributed and append-optimized checks I will keep where they are, as compatibility tests against a real cluster: the probe runs before any table is involved, and what it establishes is the expansion; where the rows land afterwards is the DISTRIBUTED BY clause's business, which the extra steps already verify.
Until Cloudberry documents a guarantee of its own, your framing is the right one: the PostgreSQL documentation covers the form, Cloudberry passes the test today, and the bridge should re-check on every connection instead of trusting a name. The change is tracked here: singhpratech/adbcbridge#83