Support Erwin Data Modeler

[jhatcher9999]

As a DBA, I would like to use Erwin Data Modeler to reverse engineer data models from CockroachDB and then push schema changes back to CockroachDB.

I tried to point Erwin to CockroachDB and found the following issues:

• I could not use the PostgreSQL JDBC interface because Erwin doesn't give me a place to enter necessary details like port number, SSL details. I was able to connect using the PostgreSQL ODBC interface against a CRDB instance using sslmode=require.
• I tried to use the "Reverse Engineer" feature and was able to see and select from a list of DBs, schemas, and tables. However, when I tried to reverse engineer a single table into the Erwin data model, I got various errors related to incompatibilities in postgres system tables (list below).

//ERROR: column "is_typed" does not exist
SELECT 1075838979,    1075852439,     (SELECT oid FROM pg_catalog.pg_type WHERE typname=user_defined_type_name)
FROM information_schema.tables
WHERE is_typed = 'YES' AND table_name = 'jim_test';

//ERROR: column "t.commit_action" does not exist
SELECT DISTINCT 1075838979,   1075852430,
                case c.relpersistence when 't' then 1 end, 1075838979,   1075852440,
                case c.relpersistence when 'u' then 'true' else 'false' end, 1075838979,   1075852418,
                (select inhparent from pg_inherits where inhrelid = c.oid), 1075838979,   1075852415,
                (select t.commit_action from information_schema.tables t where t.table_schema= 'public' and t.table_name = c.relname), 1075838979,   1075852382,
                (select spcname from pg_tablespace where oid=(case when c.reltablespace = 0 then (select oid from pg_tablespace where spcname = (select DISTINCT spcname from information_schema.tables as it join pg_database as dt on it.table_catalog = dt.datname join pg_tablespace as tb on dt.dattablespace = tb.oid)) else reltablespace end)),
                1075838979,   1075852502,         array_to_string(c.reloptions ,',',''), 1075838979,    1075849133,   c.oid
FROM pg_catalog.pg_class c
WHERE c.relnamespace = (select oid from pg_namespace where nspname = 'jim_test' ) AND c.relname = 'public' ;

//ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT  1075838979,    1073742125,   description
FROM pg_catalog.pg_description
WHERE objoid = ? AND objsubid = 0;

// ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT DISTINCT 1075839020,           1075849133,                     pcn.conrelid, 1075839020,           1073742126,                            pcn.conname, 1075839020,           1075850737,                      (select nspname from pg_catalog.pg_namespace pn where pn.oid = pcn.connamespace), 1075839020,           1075852428,              case when pcn.contype = 'x' then 1 when pcn.contype = 'c' then 0 end, 1075839020,           1075848986,                    case when pcn.contype ='x'then (select (case when (position('USING ' in pi.indexdef)) > 0 then substring(pi.indexdef from position('USING ' in pi.indexdef)) end) from pg_indexes pi where pcn.conname = pi.indexname) when pcn.contype = 'c' then pg_get_expr(conbin, conrelid) end, 1075839020,           1075850120,                   case pcn.condeferrable when true then 'true' when false then 'false' end, 1075839020,           1075852416,                      case pcn.condeferred when true then 'true' when false then 'false' end
FROM pg_constraint pcn, pg_class pc
WHERE pcn.conrelid = pc.oid AND (pcn.contype IN('c','x')) AND pc.oid = ? ;

// ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT DISTINCT 1075838985, 1073742126,                        i.relname, 1075838985, 1075849133,                 idx.indexrelid, 1075838985, 1075850519,                case idx.indisclustered when true then 'true' else 'false' end, 1075838985, 1075849459,      'true', 1075838985, 1075852398, (select amname from pg_am where oid = i.relam), 1075838985, 1075849143,    case when (position('WHERE' in pg_get_indexdef(idx.indexrelid)) > 0) then trim(leading 'WHERE' from substring(pg_get_indexdef(idx.indexrelid) from position('WHERE' in pg_get_indexdef(idx.indexrelid)))) else '' end, 1075838985, 1075853350,            case c.condeferrable when true then 0 when false then 1 end, 1075838985, 1075853351,                 case c.condeferred when true then 0 when false then 1 end, 1075838985, 1075852502,   array_to_string(i.reloptions ,',',''), 1075838985, 1075852382,            i.reltablespace, 1075838985, 1075850212,             c.conname
FROM pg_catalog.pg_index as idx
LEFT JOIN   pg_catalog.pg_constraint as c ON idx.indexrelid = c.conindid
LEFT JOIN   pg_catalog.pg_class as i  ON     i.oid = idx.indexrelid
WHERE idx.indisprimary = true AND idx.indislive = true AND c.contype = 'p' AND idx.indrelid = ? ;

//ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT 1075838981, 1073742125,  description
FROM pg_catalog.pg_description
WHERE objoid = ? AND objsubid = ? ;

//ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT DISTINCT 1075838985, 1073742126,                       i.relname, 1075838985, 1075849133,                idx.indexrelid, 1075838985, 1075849004,             'AK', 1075838985, 1075850519,               case idx.indisclustered when true then 'true' else 'false' end, 1075838985, 1075849459,     case idx.indisexclusion when true then 'true' else  (case idx.indisunique when true then 'true' else 'false' end) end, 1075838985, 1075852398,  (select amname from pg_am where oid = i.relam), 1075838985, 1075849143,      case when (position('WHERE' in pg_get_indexdef(idx.indexrelid)) > 0) then trim(leading 'WHERE' from substring(pg_get_indexdef(idx.indexrelid) from position('WHERE' in pg_get_indexdef(idx.indexrelid)))) else '' end, 1075838985, 1075853350,           case  c.condeferrable when true then 0 when false then 1 end, 1075838985, 1075853351,                case c.condeferred when true then 0 when false then 1 end, 1075838985, 1075850311,                  case idx.indisunique when true then 'true' else 'false' end, 1075838985, 1075852502,     array_to_string(i.reloptions ,',',''), 1075838985, 1075852382,             i.reltablespace
FROM  pg_catalog.pg_index as idx
LEFT JOIN pg_catalog.pg_class as i  ON  i.oid = idx.indexrelid
LEFT JOIN   pg_catalog.pg_constraint as c ON idx.indexrelid = c.conindid
WHERE idx.indislive = true AND idx.indisprimary = false AND idx.indrelid = ? ;

//ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT  DISTINCT 1075839021,   1073742126,                    pt.tgname, 1075839021,   1075849133,               pt.oid, 1075839021,   1075848978,
         case it.event_manipulation WHEN 'INSERT' then 2 when 'DELETE' then 4 when 'UPDATE' then 1 when 'TRUNCATE' then 8 end, 1075839021,   1075852454,
         case when pt.tgconstraint = 0 then 'false' else 'true' end , 1075839021,   1075852431,
         case when tgdeferrable then 'true' else 'false' end, 1075839021,   1075852432,
         case when tginitdeferred then 'true' else 'false' end, 1075839021,   1075851255,
         (select distinct p.proname from pg_trigger pt, pg_proc p where pt.tgfoid = p.oid and pt.tgname = it.trigger_name),
         1075839021,   1075848991,
         case it.action_timing WHEN 'BEFORE' then 1 when 'AFTER' then 0 when 'INSTEAD OF' then 4 end, 1075839021,   1075929114,
         case when it.action_orientation ='ROW' then 'true' else 'false' end, 1075839021,   1075929117,
         it.action_condition, 1075839021,   1075929121,
         (select event_object_column from information_schema.triggered_update_columns where trigger_name = pt.tgname)
FROM  pg_catalog.pg_trigger as pt, information_schema.triggers as it
WHERE pt.tgname = it.trigger_name AND pt.tgrelid = ? ;

//ERROR: error in argument for $1: invalid input syntax for type oid: "?";
SELECT Distinct ic.constraint_name, pc.oid, pc.confrelid, pc.conindid, ic.match_option, ic.delete_rule, ic.update_rule, case pc.condeferrable when true then 'DEFERRABLE' when false then 'NOT DEFERRABLE' end, case pc.condeferred when true then 'INITIALLY DEFERRED' when false then 'INITIALLY IMMEDIATE' end
FROM information_schema.referential_constraints as ic
LEFT OUTER JOIN pg_catalog.pg_constraint as pc ON ic.constraint_name = pc.conname
LEFT OUTER JOIN pg_catalog.pg_constraint as ref ON ic.unique_constraint_name = ref.conname
WHERE pc.conrelid = ? AND ic.constraint_schema = ?;Parameter 0 = ? ;Parameter 1 = public ;

I'm not sure if compatibility would require work on the Cockroach side only or if it would require collaboration with the Erwin developers; but it would be great if we could support some of these missing postgres tables/columns which might help with other third-party tool integrations.

I did my testing with:

• Erwin Data Modeler, Standard Edition, 64-bit, version 12.5.2, running on Windows 11 Pro
• CockroachDB Serverless, CockroachDB CCL v23.1.14 (x86_64-pc-linux-gnu, built 2024/01/16 12:37:52, go1.19.13)

Jira issue: CRDB-35684

[jhatcher9999]

On a related note, I also tried creating a data model in Erwin and then "forward engineering" the model into a CockroachDB database. This worked using the ODBC Postgres driver.