connect TDV to Cockroach database

[Joshua Smith 2]

I need help with a cockroach database datasource in TDV.

based on the documentation for cockroach (https://www.cockroachlabs.com/docs/stable/install-client-drivers.html), I can add a datasource using the supplied PostgreSQL (9.1) adapter. However, when I try to add any items, like a table, it fails.

The error provided says, "ADD Failed: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."

Detail: source SQL:

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME, CASE i.indisclustered WHEN true THEN 1 ELSE CASE am.amname WHEN 'hash' THEN 2 ELSE 3 END END AS TYPE, (i.keys).n AS ORDINAL_POSITION, pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS COLUMN_NAME, CASE am.amcanorder WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1 WHEN 1 THEN 'D' ELSE 'A' END ELSE NULL END AS ASC_OR_DESC, ci.reltuples AS CARDINALITY, ci.relpages AS PAGES, pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN (SELECT i.indexrelid, i.indrelid, i.indoption, i.indisunique, i.indisclustered, i.indpred, i.indexprs, information_schema._pg_expandarray(i.indkey) AS keys FROM pg_catalog.pg_index i) i ON (ct.oid = i.indrelid) JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) WHERE true AND n.nspname = 'public' AND ct.relname = 'fodisbursementsummary' ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION"

Attempting to execute that statement directly against my cockroach DB, I get this error: "SQL Error [42703]: ERROR: column "am.amcanorder" does not exist"

How can I get this table added

[Joshua Smith 2]

I'll answer my own question here.

I am attempting to use the supplied Postgres 9.1 adapter connect.

As of Cockroach 2.1.3, this is a known issue. The Cockroach engine is emulating Postgres and reporting its Postgres version as 9.5 (or so/below). However, the emulation layer is not complete for the 9.5 system tables and/or query syntax and, as such, the metadata query provided above fails.

I was able to work around this issue by creating a "New Adapter..." in the New Physical Data Source dialog, called cockroach, based on the "Parent Adapter" Postgres 9.1, and then modifying the configuration xml to exclude indexes (and, perhaps unnecessarily, foreign keys and primary keys). That was done by uncommenting the apprpriate attribute elements in the xml file found in the path: .../CIS_7/conf/adapters/custom/cockroach_values.xml.

I also had to restart the server before the configuration change took affect.