Postgres Protocol Plugin

ArcadeDB Server supports a subset of the Postgres wire protocol, such as connection and queries.

If you’re using ArcadeDB as embedded, please add the dependency to the arcadedb-postgresw library. If you’re using Maven include this dependency in your pom.xml file.

<dependency>
    <groupId>com.arcadedb</groupId>
    <artifactId>arcadedb-postgresw</artifactId>
    <version>26.5.1</version>
</dependency>

To start the Postgres plugin, enlist it in the server.plugins settings. To specify multiple plugins, use the comma , as separator. Example:

~/arcadedb $ bin/server.sh -Darcadedb.server.plugins="Postgres:com.arcadedb.postgres.PostgresProtocolPlugin"

If you’re using MS Windows OS, replace server.sh with server.bat.

In case of an incompatibility, restart the server with the additional option -Darcadedb.postgres.debug=true, repeat the connection attempt, and add the debug output to the issue report.

In case you’re running ArcadeDB with Docker, use -e to pass settings and open the Postgres default port 5432:

docker run --rm -p 2480:2480 -p 5432:5432 \
       --env ARCADEDB_SETTINGS="-Darcadedb.server.rootPassword=playwithdata \
          -Darcadedb.server.plugins=Postgres:com.arcadedb.postgres.PostgresProtocolPlugin " \
          arcadedata/arcadedb:latest

The Server output will contain this line:

2021-07-08 19:05:06.081 INFO  [ArcadeDBServer] <ArcadeDB_0> - Postgres Protocol plugin started

Once you have enabled the Postgres Protocol, you can interact with ArcadeDB server by using any Postgres drivers. The driver sends the queries to the ArcadeDB server without parsing or checking the syntax. For this reason, even if ArcadeDB SQL is different from Postgres SQL, you’re still able to execute any ArcadeDB SQL command through the Postgres driver. Check out the following list with the official drivers for the most popular programming languages:

For the complete list, please check Postgres website.

Other query languages

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:

{graphql}{ bookById(id: "book-1"){ id name authors { firstName, lastName } }

Example to use Cypher:

{cypher}MATCH (m:Movie)<-[a:ACTED_IN]-(p:Person) WHERE id(m) = '#1:0' RETURN *

Example of using Gremlin:

{gremlin}g.V()

Current limitations

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.

ArcadeDB does not support SSL/TLS connections over this protocol.
Both the "simple" query protocol and the extended query protocol (Parse/Bind/Describe/Execute/ Sync, what PreparedStatement uses) are supported.

System catalog and schema introspection

Since v26.9.1, queries against pg_catalog and information_schema - the ones a client’s driver sends to discover schemas, tables and columns, typically via DatabaseMetaData.getSchemas()/getTables()/getColumns() - 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.

Column type mapping

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:

  • BINARY is announced as bytea (with the standard \x<hex> text encoding), so ResultSet.getBytes()/PreparedStatement.setBytes() round-trip it as raw bytes, rather than the lossy varchar/"char"[] answers used before v26.9.1.

  • DECIMAL is announced as numeric (OID 1700), with a real binary encoder/decoder, rather than the lossy double precision or the text-only varchar used before v26.9.1 - ResultSet.getBigDecimal() round-trips the full precision. A NUMERIC value is capped at 16,000 total decimal digits (well under PostgreSQL’s own limit of 131,072 integer / 16,383 fractional digits), because ArcadeDB’s DECIMAL type has no configured precision or scale limit of its own; a value beyond this cap is declined with an error rather than accepted and silently truncated.

  • SHORT and BYTE are announced as int2 (smallint), matching the declared schema type, rather than widening to int4 (integer) as they did before v26.9.1.

  • DATE is announced as date. Before v26.9.1, a DATE property’s value on a database left at its default configuration (arcadedb.dateImplementation=java.time.LocalDate) was announced as varchar for a populated result.

  • DATETIME is announced as timestamp. The one case where a DATETIME column’s announced type can still depend on whether a row was sampled is a database explicitly configured with arcadedb.dateTimeImplementation=java.util.Date (the default is java.time.LocalDateTime): java.util.Date is also DATE’s default representation, so a bare sampled value cannot always tell the two apart on its own. The server resolves this from the schema whenever the query names a single source type, which covers ordinary column selection; a column reached through a table alias, a computed expression, or a multi-table `JOIN can still fall back to the value-only answer in that one configuration.

Transactions

Enabling auto commit to false is not 100% supported. With JDBC, leave the default settings or set:

conn.setAutoCommit(true);

Postgres Tools Known to Work

Some tools compatible with Postgres may execute queries on internal Postgres tables to retrieve the schema. Most of these queries - the ones asking about schemas, tables, columns and a few other well-known catalogs - are now answered generically (see above), but a tool asking about a pg_catalog object with no ArcadeDB equivalent (indexes, foreign keys, triggers, and similar) still gets an empty result for that specific question rather than an error. See tested compatible tools below. If the tool that you use to work with Postgres is not compatible with ArcadeDB, please open an issue.

PostgreSQL Client psql

Postgres’s psql tool works out of the box, just like with an actual Postgres server. To install this Postgres client, see here.

Connect from a terminal or console like this:

psql -h localhost -p 5432 -d mydatabase -U root

After authenticating, you can run SQL queries as normal. One can also submit the password via the environment:

PGPASSWORD=password psql -h localhost -p 5432 -d mydatabase -U root

or use the postgres protocol address:

psql postgres://username:password@host:port/database

In case the password contains special characters (like /, \, @, ?, !, &), it needs to be URL encoded (also known as "percent encoding").

Note, that in the psql console queries or commands need to be terminated with a semi-colon ; to be submitted.