Postgres Protocol Plugin

By default the Postgres driver interprets all the commands as SQL. To use another supported language, like Cypher, Gremlin, GraphQL or MongoDB, prefix the command with the language to use between curly brackets.

Example to execute a query by using GraphQL:

Example to use Cypher:

Example of using Gremlin:

The documentation about Postgres wire protocol is not exhaustive to build a bullet proof protocol. In particular the state machine. For this reason this plugin was created by reading the available documentation online (official and not official) and looking into Postgres drivers or implementations.

Since v26.9.1, queries against pg_catalog and information_schema - the ones a client’s driver sends to discover schemas, tables, columns and types, typically via DatabaseMetaData.getSchemas()/getTables()/getColumns()/getTypeInfo() - are answered by recognizing the shape of the query (which relations it names, which columns it projects, including a client’s own CASE expressions) rather than by matching one tool’s exact spelling. This means the same question is answered the same way whichever Postgres client sends it, rather than only for a short list of tools this plugin happened to be tested against. A catalog query in a shape the server does not recognize returns an empty result set rather than an error, matching how PostgreSQL itself answers a pg_catalog object it does not have.

The emulated schema list always contains exactly one schema, named after the connected database - matching SELECT current_schema() - because a PostgreSQL connection is bound to one database and sees the schemas inside it, and in ArcadeDB a connection is likewise bound to one database whose types are its tables.

Since v26.10.1, pg_type can also be queried on its own, and not only as part of a column list. Some clients build a map of every type the server has before they run a single query, and refuse to connect if that map comes back empty - the Apache Arrow ADBC PostgreSQL driver is one of them. Such a query is answered with the types this protocol can actually produce (see Column type mapping below), each described the way PostgreSQL describes it. A filter the server cannot interpret returns no rows rather than every type, so a client asking whether an extension type such as hstore exists is correctly told it does not.

Since v26.10.1, the regclass and regtype casts resolve, in both directions: 'mytable'::regclass is that type’s OID and attrelid::regclass is its name, and likewise 'int4'::regtype and atttypid::regtype for types. The function spellings to_regclass() and to_regtype() do the same. Some drivers ask for a table’s columns by writing the name as a regclass cast and comparing it against pg_class.oid - the Apache Arrow ADBC PostgreSQL driver does this in GetTableSchema - and before this the cast was ignored, so the comparison silently matched nothing and the client saw a table with no columns. A name no type carries resolves to NULL, as to_regclass() does in PostgreSQL, so asking about a table that does not exist returns nothing rather than describing every table.

Type names are matched case-sensitively first, as ArcadeDB stores them, and only then ignoring case - so 'MyType'::regclass finds the type spelled that way even though PostgreSQL would have folded the unquoted name to lower case. If two types differ only in case, an unquoted name that matches neither of them exactly resolves to nothing rather than picking one; quote the name to say which you mean.

COPY in its export direction is supported (since 26.10.1), in every form PostgreSQL accepts for it:

The query in parentheses runs as it stands, in the session’s query language (so a {cypher} prefix applies to it), and a type name with an optional column list stands for the equivalent SELECT. The rows travel in PostgreSQL’s own text, csv or binary COPY encoding, so anything that reads a PostgreSQL COPY stream reads them: psql’s `\copy (\copy (SELECT FROM Person) TO 'people.csv' CSV HEADER), pgjdbc’s CopyManager, pandas, and the Apache Arrow ADBC PostgreSQL driver, which reads every result set through COPY …​ TO STDOUT (FORMAT binary) by default and no longer needs adbc.postgresql.use_copy=false.

The option list (FORMAT, DELIMITER, NULL, HEADER, QUOTE, ESCAPE, FORCE_QUOTE, ENCODING) and the older keyword spelling (BINARY, CSV HEADER, DELIMITER AS, NULL AS, QUOTE AS, ESCAPE AS, FORCE QUOTE) are both accepted, with the same restrictions PostgreSQL applies to their combinations. The output is always UTF-8.

A COPY whose columns the schema can name is streamed row by row and is not subject to arcadedb.postgres.queryMaxRows, which is what makes it the way to export a large type over this protocol; a query whose columns only its rows can name (a schemaless type with no sample row, another query language) is materialized first, within that limit, exactly as a plain query is. The streamed form holds the query’s cursor open for as long as the client reads, which is what a bulk export needs; where that is not wanted for every user of the wire, bound the query itself with a WHERE or a LIMIT, as psql \copy users do.

Not supported, and refused with SQLSTATE 0A000 (feature_not_supported): the ingest direction COPY …​ FROM STDIN (use INSERT, or the HTTP import API), and the server-side targets COPY …​ TO 'file' and COPY …​ TO PROGRAM, which would let a wire client write files or run commands on the server host. In binary format a column without a binary encoding on this server (an array) is refused the same way; export it in text or CSV format, or project it as a string.

A column’s advertised PostgreSQL type must not depend on whether the query happens to return any rows - a client that prepares a statement against an empty result (or a schema-discovery DESCRIBE) and later re-executes it against a populated one would otherwise see the column’s type change under it. As of v26.9.1 this holds for every type ArcadeDB maps onto a native PostgreSQL OID:

In autocommit mode (the JDBC default) every statement is one transaction: a write statement that touches many records - UPDATE, DELETE, INSERT, CREATE VERTEX, CREATE EDGE - either lands whole or is rolled back whole, for vertex, edge and document types alike. The one exception is a statement that asks for chunked commits with BATCH <n>, which commits every <n> records: use it on a statement touching millions of records, since one transaction otherwise holds every modified page until the statement ends. To span several statements with one transaction, send an explicit BEGIN …​ COMMIT (or ROLLBACK).

Setting auto commit to false is not 100% supported: it does not start a transaction on its own, so a write sent after setAutoCommit(false) without a BEGIN is refused with "Transaction not begun". With JDBC, leave the default settings or set: