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 plugin supports TLS through the Postgres protocol’s own handshake, so psql, JDBC and BI tools can connect with sslmode=require. Set arcadedb.postgres.ssl to one of:

TLS uses the same key store and trust store settings as the HTTP server (arcadedb.ssl.keyStore, arcadedb.ssl.keyStorePassword, arcadedb.ssl.trustStore, arcadedb.ssl.trustStorePassword), and all four must be set. The server does not ask clients for a certificate.

(Since v26.10.2.) The user a connection authenticated as is checked again before every message: a user deleted, given a new password or stripped of the grant on the connected database is disconnected with a FATAL error, SQLSTATE 28P01. See User changes on open connections.

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.10.1, BEGIN, COMMIT and ROLLBACK sent through the extended query protocol are acknowledged the way PostgreSQL acknowledges them. Previously a libpq-based client - psycopg3, asyncpg, the Apache Arrow ADBC PostgreSQL driver - could fail to open a transaction with Failed to begin transaction. In the same release, a statement the server rejects no longer leaves the previously prepared statement usable under the same name, so a client can no longer receive the earlier statement’s rows in answer to one that was refused.

Since v26.10.1, a portal (the bound statement Execute runs) lives only as long as the transaction it was created in, as in PostgreSQL: in autocommit it ends at Sync, inside BEGIN it ends at COMMIT/ROLLBACK. Fetching a large result in chunks (JDBC setFetchSize()) therefore needs a transaction, which is what the PostgreSQL JDBC driver already requires. Binding from a prepared statement that does not exist is refused with SQLSTATE 26000, and executing or describing a portal that does not exist with 34000, instead of answering with a success message.

Since v26.11.1, a SELECT whose columns are known before its first row is read is fetched from a live cursor: each Execute (each chunk of a JDBC setFetchSize() fetch) reads only the rows it asks for, instead of the server reading the whole result before it sends the first row, and the query is not subject to arcadedb.postgres.queryMaxRows. The columns are known when the query projects only properties its type declares (SELECT name, age FROM Person), of a scalar type, BINARY, MAP or a LIST of a scalar type, or when its prepared statement was already described to the client (Describe on the statement, or a statement the PostgreSQL JDBC driver re-executes after prepareThreshold). Any other query - SELECT FROM Person, SELECT *, an expression, a property the type does not declare, another query language - can only be described from its rows, so it is still read whole first, within that limit. To fetch a large type in chunks, list the declared properties you need.

A live cursor is a running statement until it is drained or its portal ends: it is listed by list queries and SHOW TRANSACTIONS, and a cancel request or terminate query stops it, also while the client is between two fetches. Its arcadedb.command.timeout (or the session’s statement_timeout) runs from the moment the query starts to its last fetch, including the time the client takes between two fetches, where PostgreSQL restarts statement_timeout for each Execute. An idle cursor does not hold a slot of arcadedb.queryMaxConcurrent: each fetch is admitted on its own. A write the same transaction makes between two fetches can be seen by the rows not fetched yet, unless the query sorts (ORDER BY reads every row on the first fetch).

Since v26.10.1, running a prepared statement without asking for its row description again (what the PostgreSQL JDBC driver does once a statement has run prepareThreshold times, 5 by default) no longer sends an unrequested row description. Before, the JDBC connection failed with NoSuchElementException from the 6th execution on.

Since v26.10.1, when a prepared statement is described (Describe on the statement, what asyncpg, npgsql and pgx do) and the server answers that it returns no rows, running it keeps that promise. An INSERT, UPDATE, DELETE, CREATE VERTEX or CREATE EDGE without RETURN answers only its command tag with the number of affected records (e.g. INSERT 0 1), like PostgreSQL. A statement whose columns cannot be known before it runs (e.g. a {cypher} query) and that then returns rows is refused with SQLSTATE 0A000: describe the portal instead (what the PostgreSQL JDBC driver, libpq and psycopg do), which runs the statement first and reports its real columns.

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, or over a catalog relation it does not model (pg_proc, pg_trigger, pg_extension, information_schema.routines and so on), fails with SQLSTATE 0A000 (feature_not_supported) whose message names the relation, since v26.11.1. Before, it returned an empty result set, which a client reads as an empty catalog: an ungrouped SELECT count(*) FROM pg_proc WHERE …​ came back with no row at all where PostgreSQL always sends one, and psql’s `\di, psqlodbc’s SQLPrimaryKeys and SQLStatistics reported nothing. A refusal a client can see is recoverable; a wrong empty answer is not. The connection stays usable after the error. One kind of statement keeps the empty result, on purpose: a statement that names pg_catalog but reads no catalog relation, such as SELECT pg_catalog.set_config(…​), the session probes pg_dump sends.

Since v26.11.1, the type lookups a driver sends to name a type it has not met are answered like any other catalog query, from the types this protocol can produce, with the values the driver bound: the OID of a type name, the array type of an element type, the element of an array type, the name of an OID and whether its schema is on the search path. A name or an OID this server does not have is an empty answer, which is what those drivers are built to handle; one it has is no longer reported as missing. The evaluator behind them reads the constructs those lookups are written with, so a differently spelled lookup, or another client’s, is answered too: a table function in FROM (generate_series(start, stop [, step]) and unnest(array)), USING (column), = ANY(array) and <> ALL(array), a derived table joined to other relations, and one relation read through two aliases (pg_type t JOIN pg_type arr ON arr.oid = t.typarray). A series is charged to the work budget of the statement before it is built, so a lookup cannot ask for a billion rows. pg_range and pg_enum are modelled, and have no row.

Every type is a table in 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.11.1, pg_namespace (and so DatabaseMetaData.getSchemas()) also lists pg_catalog, the schema every built-in type belongs to, as PostgreSQL does: a schema browser may show it, empty.

Since v26.11.1, a materialized view and a continuous aggregate have columns in the catalog: the ones their query returns, in that order and under their aliases, so \d <view> prints them.

Since v26.11.1, each materialized view and continuous aggregate also has its _RETURN rule in pg_rewrite, as in PostgreSQL, and pg_class.relhasrules is true for it. pg_depend links that rule to its view and to every type the view reads (including types read only in a subquery), so a tool asking what is built on a table, for example before dropping it, finds the views that depend on it. Dependencies are listed per type, not per column. pg_get_ruledef(oid) prints the rule.

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.

Since v26.10.1, a query that joins a catalog relation to pg_roles or pg_user to name an owner - what a GUI browser sends to list tables with their owner - answers with the connected user rather than NULL:

Only the connected user is described as a role; an emulated catalog does not hand out the server’s account list. A query about roles, databases, privileges, character sets or collations that also mentions pg_namespace is now answered as the question it asks, instead of being answered as a schema query.

Since v26.11.1, psql’s `\dt, \d <table> and \d+ <table> list and describe the database’s types, and the PostgreSQL ODBC driver (psqlodbc) answers SQLColumns and SQLTables(SQL_ALL_TABLE_TYPES). Before, \dt printed Did not find any relations, SQLColumns returned no columns and SQLTables(SQL_ALL_TABLE_TYPES) failed with a syntax error. The catalog queries these clients send are now read as PostgreSQL reads them:

A projection that names two columns alike, such as SELECT NULL, NULL, relkind, returns both columns.

A catalog query that would take more work than the server spends on one statement - a regular expression that backtracks heavily, or deeply nested correlated sub-selects - fails with SQLSTATE 54000 (program_limit_exceeded) instead of returning an empty result.

Since v26.11.1, information_schema.columns.data_type answers the SQL standard name, as format_type() does (integer, character varying, double precision, timestamp without time zone), and the literal ARRAY for any array column. udt_name keeps the pg_type name (int4, varchar, float8, _text), so the two columns differ exactly where PostgreSQL’s do. The precision columns the relation declares are written for the built-in types that have them: numeric_precision (16, 32 and 64 for the integers, 24 for real, 53 for double precision), numeric_scale (0 for the integers), datetime_precision (6 for a timestamp, 0 for a date) and character_octet_length (2^30 for a text column with no declared length). numeric_precision_radix is NULL for a type that is not a number, where it used to answer 2.

Since v26.11.1, a catalog query that matches no row describes its columns exactly as one that matches: every column of a catalog relation has a fixed type (pg_class.oid is always int4, relhasindex always bool). A pg_type lookup of a type that does not exist returns the columns it asked for with no rows. (Before, such a lookup returned no columns at all, and an empty answer from another relation announced every column as varchar, issue #10869.)

Since v26.11.1, the indexes of the database are in the catalog, so psql’s `\d <table> prints the Indexes: section, \d <index> describes an index, \di lists them, and the index queries of psqlodbc and of the GUI tools are answered:

The PostgreSQL JDBC driver’s DatabaseMetaData.getIndexInfo() and getPrimaryKeys() - what Hibernate, Flyway, Liquibase and BI tools call - are answered too. They list an index’s key columns with the set-returning function information_schema._pg_expandarray() (a row for each element of the array, the calls of one row in step) and read its members as (expression).n; the catalog reads those, SQL’s trim(both '"' from …​) and the bitwise & the driver uses to find a descending key. getPrimaryKeys() returns no row, as ArcadeDB has no primary key. A set-returning call under GROUP BY or an aggregate is not read.

A text compared with a flag is read as the boolean it spells (indisprimary = 't').

An index key over a HIDDEN property is not a column of its table in the catalog, so it is described the way PostgreSQL describes an index on an expression: its column is named expr, and a unique index with such a key is not listed as a unique constraint.

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. Quote the name if it is not a plain identifier - COPY "My Type" TO STDOUT - and any character is allowed in it, backslashes and backticks included (fixed in 26.10.1: such a name used to be misread or refused). 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.

The exported columns are fixed before the first row goes out, the way RowDescription fixes them for a query. For a whole type they are every property the type declares, inherited ones included, plus whatever a sample row adds on top - a declared property is exported even when the first row does not have one. Because ArcadeDB types can hold rows of different shapes, that set is a good answer but not a provable one: if a row turns out to carry a property the export never announced, the COPY fails with SQLSTATE 0A000 naming that column, rather than writing the row without it. List the columns yourself to make the set exact and the export complete:

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:

Since v26.10.1, a column is typed from all the rows of a result, not from the first one, and a list from all its elements: [1, 2.5] is announced as float8[], [1, 3000000000] as int8[], and a list that also holds a DECIMAL as numeric[]. A list or column that mixes values no common number type can hold is announced as text.

Since v26.10.1, a prepared statement keeps ONE layout for all its rows, because a client may describe it once and reuse that description. A property the type does not declare (ArcadeDB is schemaless) can hold an integer in one record and a long, a double or a string in the next, so such a property is announced as varchar (a list as text[]) when the statement is described (Describe on the statement, or on a portal of a named statement, which is what the PostgreSQL JDBC driver does from its prepareThreshold-th execution). Declared properties keep their exact native type. To get a native type back for a property in a prepared statement, declare it, for example CREATE PROPERTY Event.score INTEGER.

Since v26.11.1, pg_type also lists the array types of int2, bytea, bpchar, varchar, date and timestamp, and a parameter bound as one of them is accepted - for example the varchar[] the PostgreSQL JDBC driver sends for Connection.createArrayOf("varchar", …​), which used to fail.

Since v26.11.1, an array parameter sent in text format (a {…​} literal, what a client that does not negotiate the binary format sends) is read as PostgreSQL reads it. An unquoted NULL element is a SQL NULL, while "NULL" is the string NULL. An empty element "" is kept. A malformed literal, such as {a,,b}, is refused with malformed array literal instead of being stored as something else. A parameter sent without a type is read as an array only when it is a valid array literal, so a JSON document such as {"k": 1} stays a string. (Before, an empty element was dropped and NULL was stored as the string NULL, issue #10870.)

Since v26.11.1, every type of the database (a table or a materialized view in the catalog) also has its row type in pg_type, as every PostgreSQL table has: a composite type (typtype = 'c', typcategory = 'C') named after the type, in its schema, whose typrelid is the relation’s pg_class.oid. pg_class.reltype holds its OID, so the join pg_class c JOIN pg_type t ON t.oid = c.reltype finds it, and format_type(c.reltype, NULL) and c.reltype::regtype print the relation’s name. Only an index has reltype = 0. A column’s pg_attribute.attcollation, and each key of pg_index.indcollation, is the collation of its type: 100 (default) for text, varchar, bpchar and their arrays, 0 for every other type, as pg_type.typcollation says. (Before, reltype and attcollation were 0 for every relation and column, so the join from a table to its row type answered no rows, issue #10873.) A client that enumerates pg_type now reads one more row per type of the database.

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

A client that PIPELINES several statements before waiting for their answers - a JDBC executeBatch(), psycopg3 pipeline mode, asyncpg - groups them differently (since 26.10.1): everything between two Sync messages is one transaction, so a batch either lands whole or leaves nothing behind, and the statements after the one that failed are not run at all. This is what PostgreSQL does and what BatchUpdateException handling assumes. Before 26.10.1 each statement in such a batch committed on its own, so a failure halfway left the earlier ones persisted; if you relied on that, split the batch into separate round trips.

Since v26.10.1 a transaction-control statement is recognized in every spelling ArcadeDB SQL accepts, not only the bare keyword: with a leading or trailing comment (as added by sqlcommenter, OpenTelemetry or connection-pool routing hints, e.g. COMMIT /traceparent='…​'/), with extra ;, BEGIN ISOLATION <level> (which opens the transaction with that isolation level, e.g. BEGIN ISOLATION REPEATABLE_READ) and COMMIT RETRY <n> (a plain COMMIT outside a SQL script, which has no block to retry). A COMMIT that fails, for example on a duplicated unique key, reports the error and ends the transaction, as in PostgreSQL: the session is back in idle state and no ROLLBACK is needed.

Since v26.10.2 PostgreSQL’s own transaction-control spellings are accepted too, as ORMs and drivers send them:

ISOLATION LEVEL READ COMMITTED and READ UNCOMMITTED open a READ_COMMITTED transaction (in PostgreSQL too READ UNCOMMITTED behaves as READ COMMITTED), and REPEATABLE READ a REPEATABLE_READ one. What this server cannot honor is refused with SQLSTATE 0A000 (feature not supported) instead of a syntax error: ISOLATION LEVEL SERIALIZABLE, READ ONLY, and COMMIT AND CHAIN / ROLLBACK AND CHAIN. In v26.10.1 and earlier only ArcadeDB’s SQL spellings above were recognized.

Savepoints are not supported. SAVEPOINT <name> and RELEASE [SAVEPOINT] <name> are accepted and do nothing, but ROLLBACK TO [SAVEPOINT] <name> is refused with SQLSTATE 0A000 (feature not supported) and the transaction is marked aborted, so the client must ROLLBACK it. (Since v26.10.1; before, ROLLBACK TO SAVEPOINT reported success while every write made since the savepoint stayed pending and was persisted by the next COMMIT.) An ORM or driver that wraps statements in savepoints (for example a nested transaction.atomic() in Django, or psql’s ON_ERROR_ROLLBACK) only works as long as it never rolls back to one.

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:

SET changes a setting for the current connection only: it never changes the database or any other connection, and SHOW reads it back. Since v26.10.1 it follows PostgreSQL’s rules:

Some settings describe what the server does and cannot be changed: dates always travel in ISO format (DateStyle keeps only the field order you choose) and text is always UTF8. A SET of a read-only setting such as server_version is refused.

String literals are read as PostgreSQL reads them. With standard_conforming_strings on, the default and what the server reports to drivers, a backslash in a plain '…​' literal is an ordinary character ('C:\' is the three characters C:\) and a quote inside a literal is doubled ('it''s'). Backslash escapes are processed in an E'…​' escape string (E’a\tb' holds a tab), with PostgreSQL’s escape set: \b \f \n \r \t, octal \ooo, hex \xhh, \uXXXX and \UXXXXXXXX. SET standard_conforming_strings = off (or the same option in the startup packet, e.g. pgjdbc’s options=-c standard_conforming_strings=off) makes every plain literal an escape string too, as in PostgreSQL. Inside an ArcadeDB JSON object (CONTENT {…​}) a single-quoted string keeps the ArcadeDB meaning.

A SET is answered SET only when it did what it asked. Since v26.10.1 every other SET is answered with an error, on both the simple and the extended query protocol, rather than accepted and ignored:

Since v26.10.2 a cancel request works as in PostgreSQL. The CancelRequest that JDBC’s Statement.cancel(), pgjdbc’s setQueryTimeout() and libpq’s PQcancel send stops the statement the connection is running: it fails with SQLSTATE 57014 (query_canceled), what it wrote is rolled back as after any other failed statement, and the connection stays open for the next one. A connection that is not running a statement is not affected.

Each connection gets its own backend process id and a random 32-bit secret key, which the client receives at connect time (BackendKeyData) and must quote in its cancel request. The statements a connection runs are listed by the HTTP list queries command and by Cypher SHOW TRANSACTIONS with protocol postgres and the process id as connectionId, and stopped by terminate query or TERMINATE TRANSACTIONS with the same 57014.

As on PostgreSQL, a cancel request is not authenticated: the secret key is its only credential, and process ids are sequential. A cancel request that quotes a live connection with the wrong secret stops nothing and gets no answer; the server logs it, at WARNING at most once a minute with the number refused since, so guessing is visible in the log. A cancel is best effort, as on PostgreSQL: one that arrives between two statements stops nothing, and one that crosses the start of the next statement may stop that one.

Three SQL functions work on connections by process id, as their PostgreSQL namesakes:

root may signal every connection, any other user only their own. Both signal functions answer false for a process id that names no connection the caller may signal. They answer NULL for a NULL process id and fail on a fractional one (such as 1.5), as on PostgreSQL. They signal only connections of the server that runs them, and are registered with the SQL functions of the JVM once the PostgreSQL plugin starts, so SQL sent over any protocol can call them.

(Since v26.11.1.) A statement that creates, or renames onto, a name that is already taken fails with the SQLSTATE PostgreSQL uses for the same case: 42P07 (duplicate_table) for a type, an index or a view, 42701 (duplicate_column) for a property, and 42710 (duplicate_object) for anything else, such as a constraint, a bucket, a trigger or a function. Before v26.11.1 it was XX000 (internal error), or 42P01 (undefined table) for some of them.