Add CockroachDB compatibility

[dvarrazzo]

CockroachDB is a distributed database using the same BE-FE protocol of PostgreSQL. As such it can communicate using the libpq and it is roughly compatible with psycopg.

We are interested in ensuring support to CRDB in psycopg. Currently the work is happening in the crdb branch. We are customizing the test suite in order to skip or adapt tests according to the different features and adding crdb-specific tests.

In the current implementation, we should expose crdb-specific objects in the psycopg.crdb module, which might be made fully DBAPI compatible. We have explored the possibility to automatically detect the database type from psycopg.connect() but it doesn't seem a good idea from the point of view of types annotation (we can annotate psycopg.crdb.connect() as returning a CrdbConnection class; we cannot do it for psycopg.connect()) and because the pattern might not scale and slow down connection should, in the future, add support to different vendors. Because the databases behaviour might be considerably different, is seems a good idea to be explicit about it.

We have already found a couple of bugs in psycopg, by trying to run the test suite against crdb: #309 and the COPY formatting error fixed in 76a528c.

We are adding a connection.info.vendor attribute in order to make simple to tell apart the database we are connected to. Some of the psycopg objects are subclassed in order to either provide a more extended interface (for instance CrdbConnectionInfo exposes a crdb_version attribute) or to provide different defaults or produce different classes instances (for instance CrdbConnection.info returns a CrdbConnectionInfo instead of a ConnectionInfo.

CrdbConnection is, by default, configured with adapters (dumpers/loaders) to manage correctly the CRDB data types and their differences from Postgres; the mapping can be customized the same way it is on PG connections.

[AAS97]

Hi @dvarrazzo,
Amazing work so far ! What is the status on this issue ? I'm really looking forward to 3.1, and it seems that this enhancement is the last blocker in achieving 3.1 milestone.

[dvarrazzo]

Hello @AAS97

I was waiting for release 22.1.3, fixing a few issues found during psycopg 3 integration. It seems that such version was released yesterday: I'll verify the state of the integration.

The goal is to release 3.1 in July, regardless of the CRDB state (the interface is ready and what doesn't work now can be fixed in future releases).

[dvarrazzo]

Merged to master! 👋