Develop CockroachDB compatibility

[pematt]

Feature description
This feature request is to develop the capability in pgModeler to import and diff CockroachDB databases. Since CockroachDB is supposed to be PostgreSQL compliant there may be very little work involved in making pgModeler CochroachDB compatible. By making pgModeler CockroachDB compatible a whole new market would be opened up for this tool.

CockroachDB is built on the premise of having an interface compatible with PostgreSQL. However, there are some features in PostgreSQL which do not (yet) exist in CockroachDB. Some of them are features that can be controlled by the user, eg do not use triggers, others are that at least one function used by pgModeler does not appear to exist which would require a pgModeler code change.

I have performed one experiment which was partly successful - connection worked, diff code generated on a PostgreSQL DB executed on a CockroachDB instance worked, but a diff with a CockroachDB instance failed after 49 statements, see details below.

Sample image
Connection success:

49 statements successfully executed by pgModeler in a CockroachDB:

Experiment

• Created a database in the CockroachDB cloud.
• Imported the CockroachDB SSL cert to the local machine.
• Set up a CockroachDB connection in pgModeler, with SSL Mode Allow, the Connection DB defaultdb, the rest of the connection info from the connect string generated when creating the CockroachDB instance, and verified that the connection worked.
• Created a DB in pgModeler
  • that had a role with the same name as the role in the CockroachDB instance,
  • and it had two schemas and 3 tables owned by the role created above.
• Performed a diff with the CockroachDB. pgModeler managed to perform 49 statements in the CockroachDB, but eventually there was a problem:
  • ERROR: unknown function: pg_tablespace_location(): function undefined
• Instead, generated a diff with a local empty PostgreSQL database.
• Connected to the CockroachDB instance using psql.
• Pasted the generated diff into the psql CockroachDB connection, all commands were successfully executed.

Default CockroachDB databases:

$ psql postgresql://<user>:<pwd>@<db>.gcp-europe-west1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1), server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off)
Type "help" for help.

defaultdb=> select datname from pg_database;
  datname  
-----------
 defaultdb
 postgres
 system
(3 rows)

Stack trace:

[0] src/tools/modeldatabasediffform.cpp (893)
  void ModelDatabaseDiffForm::captureThreadError(Exception)
    [SQLCommandNotExecuted] Could not execute the SQL command.
 Message returned: `ERROR:  unknown function: pg_tablespace_location(): function undefined
'

[1] src/tools/databaseimporthelper.cpp (705)
  void DatabaseImportHelper::importDatabase()
    [SQLCommandNotExecuted] Could not execute the SQL command.
 Message returned: `ERROR:  unknown function: pg_tablespace_location(): function undefined
'
       ** catalog: tablespace

[2] src/catalog.cpp (986)
  std::vector<std::map<QString, QString> > Catalog::getObjectsAttributes(ObjectType, const QString&, const QString&, const std::vector<unsigned int>&, attribs_map)
    [SQLCommandNotExecuted] Could not execute the SQL command.
 Message returned: `ERROR:  unknown function: pg_tablespace_location(): function undefined
'
       ** catalog: tablespace

[3] src/catalog.cpp (847)
  std::vector<std::map<QString, QString> > Catalog::getMultipleAttributes(ObjectType, attribs_map)
    [SQLCommandNotExecuted] Could not execute the SQL command.
 Message returned: `ERROR:  unknown function: pg_tablespace_location(): function undefined
'

[4] src/catalog.cpp (546)
  void Catalog::executeCatalogQuery(const QString&, ObjectType, ResultSet&, bool, attribs_map)
    [SQLCommandNotExecuted] Could not execute the SQL command.
 Message returned: `ERROR:  unknown function: pg_tablespace_location(): function undefined
'
       ** catalog: tablespace

[5] src/connection.cpp (461)
  void Connection::executeDMLCommand(const QString&, ResultSet&)
    [SQLCommandNotExecuted] Could not execute the SQL command.
 Message returned: `ERROR:  unknown function: pg_tablespace_location(): function undefined
'
       ** 42883

[rkhaotix]

pgModeler relies on original PostgreSQL system catalogs (tables, views, functions) to run the database reverse engineering. That's why I don't guarantee that the mentioned process will work on the forks of the RDBMS. I don't know CrockroachDB but obviously, it lacks some needed pieces so the import/diff features work without errors.

If your problem is only the absence of pg_tablespace_location(), you have two options:

1. Ignore import errors by checking the related option in the database import/diff dialog.
2. Tweak the schema file [PGMODELER_INSTALLATION_ROOT]/schemas/catalog/tablespace.sch replacing the lines 24 and 25:

[SELECT oid, spcname AS name, spcacl AS permission, spcowner AS owner, 
     pg_tablespace_location(oid) AS directory, ]

By:

[SELECT oid, spcname AS name, spcacl AS permission, spcowner AS owner, 
    '/foo/bar' AS directory, ]

Note that a non-empty value is needed for the field directory, and more importantly, I still don't guarantee that you'll get the desired results because I don't know if pgModeler is running the import/diff as designed! :)

[pematt]

Ok, thanks for looking into it!

[pematt]

I tried with the suggested solution, then the diff process seemed to work for longer, but then there was a segmentation fault. How can I find out where the segfault was?

[rkhaotix]

Segmentation fault means bug. I need to debug pgModeler running the diff in a copy of your database and see where it is crashing. :)

Or if you can create a small sample model/dump where the crash happens I can use it too.

[pematt]

Here are the steps to reproduce:

1. Compile pgModeler as you described above
2. Create a free tier cockroachdb at cockroachlabs.com (no payment details needed)
3. Set up the access in pgModeler to the DB created in step 2, using defaultdb as the database name and allow ssl
4. Create a model in pgModeler with one schema and one table with a pk and some columns
5. Perform a diff with the cockroachdb DB from step 2
6. pgModeler starts doing the diff, without any visual cues, and after about 45 seconds it dumps

Here is the model I used:
test.dbm.zip

I can send you the dump too if you tell me where to look for it, I'm using Linux Mint.

[pematt]

I think it dumped because the test.dbm model above has partitioning set for the table which does seem to be problematic in cockroachdb, I don't know the details yet. Sorry I had forgotten that I had set partitioning.

I tried another model without partitioning then the diff progress dialogue came up and showed the progress, it went through all partitions also the system partitions, then right at the end it dumped.

I then did a partial diff only on one schema (with do not drop missing objects) and then the diff process successfully completed! 👍

The create role statement is incomplete but the rest of the statements are correct. The reason that the create role statement is incomplete may be because I did not give the role any privileges in the pgModeler model.

Here is the model for which a partial diff only on schema test4 worked:
test4.dbm.zip

Here is the complete generated diff:

-- Diff code generated with pgModeler (PostgreSQL Database Modeler)
-- pgModeler version: 1.1.0
-- Diff date: 2024-02-23 06:44:24
-- Source model: new_database
-- Database: defaultdb
-- PostgreSQL version: 13.0

-- [ Diff summary ]
-- Dropped objects: 0
-- Created objects: 3
-- Changed objects: 0

SET search_path=public,pg_catalog,test4;
-- ddl-end --


-- [ Created objects ] --
-- object: cdbdba7105 | type: ROLE --
-- DROP ROLE IF EXISTS cdbdba7105;
CREATE ROLE cdbdba7105 WITH ;
-- ddl-end --

-- object: test4 | type: SCHEMA --
-- DROP SCHEMA IF EXISTS test4 CASCADE;
CREATE SCHEMA test4;
-- ddl-end --
ALTER SCHEMA test4 OWNER TO cdbdba7105;
-- ddl-end --

-- object: test4.test4 | type: TABLE --
-- DROP TABLE IF EXISTS test4.test4 CASCADE;
CREATE TABLE test4.test4 (
	id int8,
	partition text,
	created_at timestamptz DEFAULT now()

);
-- ddl-end --
ALTER TABLE test4.test4 OWNER TO cdbdba7105;
-- ddl-end --