Postgres Wire Protocol

CrateDB contains experimental support for the PostgreSQL wire protocol v3.

If a node is started with postgres support enabled it will bind to port 5432 by default. To use a custom port, set the corresponding Ports in the Configuration.

However, even though connecting PostgreSQL tools and client libraries is supported, the actual SQL statements have to be supported by CrateDB’s SQL dialect. A notable difference is that CrateDB doesn’t support transactions, which is why clients should generally enable autocommit.

Note

In order to use setFetchSize in JDBC it is possible to set autocommit to false.

The client will utilize the fetchSize on SELECT statements and only load up to fetchSize rows into memory.

See PostgreSQL JDBC Query docs <https://jdbc.postgresql.org/documentation/head/query.html> for more information.

Write operations will still behave as if autocommit was enabled and commit or rollback calls are ignored.

Server Compatibility and Implementation Status

CrateDB emulates PostgreSQL server version 9.5

Start-Up

SSL Support

SSL support is only available in the Enterprise Edition. If enterprise is disabled, all SSLRequests are answered with N, indicating to the client that SSL is not available.

Authentication

If the Enterprise Edition is enabled, authentication methods can be configured using Host Based Authentication (HBA).

If the enterprise functionality is disabled, all “start up” requests are answered with an AuthenticationOK response.

Database selection

Since CrateDB uses schemas instead of databases, the database parameter sets the default schema name for future queries. If no schema is specified, the schema doc will be used as default. Additionally, the only supported charset is UTF8.

Simple Query

The Simple Query protocol is implemented with the following limitations:

  • The query may only contain one query. Sending multiple queries with one message is not supported.

Extended Query

The Extended Query protocol is implemented with the following limitations:

  • describe messages for an unbound prepared statement always result in a NoData message instead of a ParameterDescription.
  • To optimize the execution of bulk operations the execution of statements is delayed until the Sync message is received

Copy Operations

CrateDB does not support the COPY sub-protocol.

Function Call

The function call sub-protocol is not supported since it’s a legacy feature.

Canceling Requests

Operations can be cancelled using the KILL statement, hence the CancelRequest message is unsupported. Consequently, the server won’t send a BackendKeyData message during connection initialization.

pg_type

Some clients require the pg_catalog.pg_type in order to be able to stream arrays or other non-primitive types.

For compatibility reasons there is a trimmed down pg_type table available in CrateDB:

cr> select * from pg_catalog.pg_type order by oid;
+------+----------+---------+-------------+
|  oid | typdelim | typelem | typname     |
+------+----------+---------+-------------+
|   16 | ,        |       0 | bool        |
|   18 | ,        |       0 | char        |
|   20 | ,        |       0 | int8        |
|   21 | ,        |       0 | int2        |
|   23 | ,        |       0 | int4        |
|  114 | ,        |       0 | json        |
|  199 | ,        |     114 | _json       |
|  700 | ,        |       0 | float4      |
|  701 | ,        |       0 | float8      |
| 1000 | ,        |      16 | _bool       |
| 1002 | ,        |      18 | _char       |
| 1005 | ,        |      21 | _int2       |
| 1007 | ,        |      23 | _int4       |
| 1015 | ,        |    1043 | _varchar    |
| 1016 | ,        |      20 | _int8       |
| 1021 | ,        |     700 | _float4     |
| 1022 | ,        |     701 | _float8     |
| 1043 | ,        |       0 | varchar     |
| 1184 | ,        |       0 | timestampz  |
| 1185 | ,        |    1184 | _timestampz |
+------+----------+---------+-------------+
SELECT 20 rows in set (... sec)

Show Transaction Isolation

For compatibility with JDBC the SHOW TRANSACTION ISOLATION LEVEL statement is implemented:

cr> show transaction isolation level;
+-----------------------+
| transaction_isolation |
+-----------------------+
| read uncommitted      |
+-----------------------+
SHOW 1 row in set (... sec)

Client Compatibility

JDBC

pgjdbc JDBC drivers version 9.4.1209 and above are compatible.

Limitations

  • reflection methods like conn.getMetaData().getTables(...) won’t work since the required tables are unavailable in CrateDB.

    As a workaround it’s possible to use SHOW TABLES or query the information_schema tables manually using SELECT statements.

  • OBJECT and GEO_SHAPE columns can be streamed as JSON but require pgjdbc version 9.4.1210 or newer.

  • Multidimensional arrays will be streamed as JSON encoded string to avoid a protocol limitation where all sub-arrays are required to have the same length.

  • The behavior of PreparedStatement.executeBatch in error cases depends on in which stage an error occurs: A BatchUpdateException is thrown if no processing has been done yet, whereas single operations failing after the processing started are indicated by an EXECUTE_FAILED (-3) return value.

  • Transaction limitations as described above.

  • Having escape processing enabled could prevent the usage of Object Literals in case an object key’s starting character clashes with a JDBC escape keyword (see also JDBC escape syntax). Currently, disabling escape processing will remedy this, but prevent the Extended Query API from working due to a bug at pgjdbc.

Connection Failover and Load Balancing

Connection failover and load balancing is supported as described here: PostgreSQL JDBC connection failover.

Note

It is not recommended to use the targetServerType parameter since CrateDB has no concept of Master/Slave nodes.