crdb -- CockroachDB support

.. module:: psycopg.crdb

.. versionadded:: 3.1

CockroachDB_ is a distributed database using the same fronted-backend protocol of PostgreSQL. As such, Psycopg can be used to write Python programs interacting with CockroachDB.

.. _CockroachDB: https://www.cockroachlabs.com/

Opening a connection to a CRDB database using psycopg.connect() provides a largely working object. However, using the psycopg.crdb.connect() function instead, Psycopg will create more specialised objects and provide a types mapping tweaked on the CockroachDB data model.

.. _crdb-differences:

Main differences from PostgreSQL

CockroachDB behaviour is different from PostgreSQL__: please refer to the database documentation for details. These are some of the main differences affecting Psycopg behaviour:

.. __: https://www.cockroachlabs.com/docs/stable/postgresql-compatibility.html

• ~psycopg.Connection.cancel() doesn't work before CockroachDB 22.1. On older versions, you can use CANCEL QUERY_ instead (but from a different connection).

• :ref:server-side-cursors are well supported only from CockroachDB 22.1.3.

• ~psycopg.ConnectionInfo.backend_pid is only populated from CockroachDB 22.1. Note however that you cannot use the PID to terminate the session; use SHOW session_id_ to find the id of a session, which you may terminate with CANCEL SESSION_ in lieu of PostgreSQL's :sql:pg_terminate_backend().

• Several data types are missing or slightly different from PostgreSQL (see adapters for an overview of the differences).

• The :ref:two-phase commit protocol <two-phase-commit> is not supported.

• :sql:LISTEN and :sql:NOTIFY are not supported. However the CHANGEFEED_ command, in conjunction with ~psycopg.Cursor.stream(), can provide push notifications.

.. _CANCEL QUERY: https://www.cockroachlabs.com/docs/stable/cancel-query.html .. _SHOW session_id: https://www.cockroachlabs.com/docs/stable/show-vars.html .. _CANCEL SESSION: https://www.cockroachlabs.com/docs/stable/cancel-session.html .. _CHANGEFEED: https://www.cockroachlabs.com/docs/stable/changefeed-for.html

.. _crdb-objects:

CockroachDB-specific objects

.. autofunction:: connect

This is an alias of the class method CrdbConnection.connect.

If you need an asynchronous connection use the AsyncCrdbConnection.connect() method instead.

.. autoclass:: CrdbConnection

`psycopg.Connection` subclass.

.. automethod:: is_crdb

    :param conn: the connection to check
    :type conn: `~psycopg.Connection`, `~psycopg.AsyncConnection`, `~psycopg.pq.PGconn`

.. autoclass:: AsyncCrdbConnection

`psycopg.AsyncConnection` subclass.

.. autoclass:: CrdbConnectionInfo

The object is returned by the `~psycopg.Connection.info` attribute of
`CrdbConnection` and `AsyncCrdbConnection`.

The object behaves like `!ConnectionInfo`, with the following differences:

.. autoattribute:: vendor

    The `CockroachDB` string.

.. autoattribute:: server_version

.. data:: adapters

The default adapters map establishing how Python and CockroachDB types are
converted into each other.

The map is used as a template when new connections are created, using
`psycopg.crdb.connect()` (similarly to the way `psycopg.adapters` is used
as template for new PostgreSQL connections).

This registry contains only the types and adapters supported by
CockroachDB. Several PostgreSQL types and adapters are missing or
different from PostgreSQL, among which:

- Composite types
- :sql:`range`, :sql:`multirange` types
- The :sql:`hstore` type
- Geometric types
- Nested arrays
- Arrays of :sql:`jsonb`
- The :sql:`cidr` data type
- The :sql:`json` type is an alias for :sql:`jsonb`
- The :sql:`int` type is an alias for :sql:`int8`, not `int4`.