sql: navicat compatibility

[joel-solymosi]

To repro:

• Open navicat ( http://www.navicat.com/download/navicat-for-postgresql )
• Attempt to connect to a cockroachdb instance

Expected results:

• Displays cockroach server's databases

Observed results:

• ERROR: sql/select.go:354 (pmattis): unsupported FROM: FROM pg_database AS d LEFT JOIN pg_tablespace AS t ON d.dattablespace = t.oid

Adding support for database / table auto-discovery would allow the entire postgres toolchain / ecosystem to work against cockroach, which might significantly increase adoption.

[petermattis]

@joel-custlabs CockroachDB != PostgreSQL. The proximate cause of the error you encountered is that CockroachDB currently doesn't support joins, though this is on the near-term roadmap to be fixed. More problematic is that CockroachDB does not support the postgres system tables (e.g. pg_database and pg_tablespace). There are no plans to attempt compatibility for these tables. I agree that such compatibility would ease/increase adoption, yet it isn't clear how much work is required there (the surface area appears huge).

[digipigeon]

Hi, just wanted to know if there is any update on this, as I see that basic joins are now supported.

+1 on this feature from me.

[jordanlewis]

We've also added support for pg_database to support common ORMs like Hibernate.

CockroachDB doesn't currently have a notion of tablespaces. We've implemented some dummy support for other pg_* tables, though, so it doesn't seem out of the question that we could do the same thing for pg_tablespace.

Can you determine whether a trivial implementation of pg_tablespace would be sufficient for these introspection queries? That would be helpful for us to figure out the prioritization and effort level of this issue.

[digipigeon]

Currently the query which is failing is as follows

[2016-11-22 21:05:46.206] [000000] [Cockroach] [PGSQL]
SELECT d.datname, d.oid, pg_get_userbyid(d.datdba) AS owner, shobj_description(d.oid, 'pg_database') AS comment, t.spcname, d.datacl, d.datlastsysoid, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace=t.oid

[2016-11-22 21:05:46.240] [000000] [Cockroach] [PGSQL]
ERROR:  table "pg_tablespace" does not exist

I am unsure on what specifics would need to be implemented to make this function as expected.

[jordanlewis]

Thanks for tracking down this query. Besides requiring pg_tablespace, it also requires support for a few other postgres-specific functions, tables, and other features that we haven't implemented:

• [x] pg_get_userbyid. This returns the name of the user who owns a database.
• [x] shobj_description. This seems like it just returns a comment.
• [x] obj_description. This seems like it just returns a comment.
• [x] pg_encoding_to_char. This seems to return a human-readable description of an encoding scheme.
• [x] pg_inherits
• [x] pg_foreign_table
• [x] pg_foreign_server
• [x] Correlated subqueries: #3288
• [x] String to array casts
• [x] pg_get_viewdef

It seems like this query is just returning metadata to display in a UI, and thus could be pretty easily faked.

[digipigeon]

Thanks for dissecting this, is there any possibility of creating dummy functions to allow this query to complete?

[jordanlewis]

Yes, we can likely get to this at some point soon.

There's one more thing you can do to help us help you quicker, if you have the time: perform a sample run of navicat against a sample postgres database and log the queries. If you put those queries in a gist and link it here we can finish the issue triage - it would be a shame if we implemented these three functions only to run into a stumbling block in the next query that navicat executes after the one you pasted.

[digipigeon]

Looks like it may be a bit more complicated:

[2016-11-23 12:52:16.623] [007691] [Cockroach] [PGSQL]
SET application_name = 'NAVICAT'

[2016-11-23 12:52:16.651] [007691] [Cockroach] [PGSQL]
SELECT d.datname, d.oid, pg_get_userbyid(d.datdba) AS owner, shobj_description(d.oid, 'pg_database') AS comment, t.spcname, d.datacl, d.datlastsysoid, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace=t.oid

Selecting a database

[2016-11-23 12:52:21.131] [007692] [Cockroach] [PGSQL]
SET application_name = 'NAVICAT'

[2016-11-23 12:52:21.149] [007692] [Cockroach] [PGSQL]
SELECT nspname, oid, pg_get_userbyid(nspowner) AS owner, nspacl, obj_description(oid) FROM pg_namespace

[2016-11-23 12:52:23.843] [007692] [Cockroach] [PGSQL]
SELECT c.oid, obj_description(c.oid),  c.relhasoids AS hasoids, n.nspname AS schemaname, c.relname AS tablename, c.relkind, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers, ft.ftoptions, fs.srvname, c.relacl, c.reltuples, ((SELECT count(*) FROM pg_inherits WHERE inhparent = c.oid) > 0) AS inhtable, i2.nspname AS inhschemaname, i2.relname AS inhtablename, c.reloptions AS param, c.relpersistence AS unlogged FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace LEFT JOIN (pg_inherits i INNER JOIN pg_class c2 ON i.inhparent = c2.oid LEFT JOIN pg_namespace n2 ON n2.oid = c2.relnamespace) i2 ON i2.inhrelid = c.oid LEFT JOIN pg_foreign_table ft ON ft.ftrelid = c.oid LEFT JOIN pg_foreign_server fs ON ft.ftserver = fs.oid WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 'f'::"char")) AND n.nspname = 'public'

[jordanlewis]

Thanks - this exposes several more missing features.

From pg_catalog we're missingpg_inherits, pg_foreign_table, and pg_foreign_server.

We also don't support correlated subqueries yet, like the inner COUNT(*) query in the last line you pasted. We are planning on implementing these but they probably won't be ready before Q2 of 2017.

[knz]

@digipigeon any chance you'd know why navicat is looking at pg_foreign_server? In this specific instance the entire query would return no row whatsoever.

[digipigeon]

@knz sorry for the late reply, I have no idea, only an end-user of Navicat.

[jordanlewis]

We're making some progress on this. The main blocker is still correlated subqueries.

[jake2009]

I expect to see CockroachDB will compatible to a powerful ui tool . I hope it is easy to learn to use CockroachDB . If CockroachDB have a powerful ui tool , then it will be easy to learn ,to use . Many people create a table by ui not by command . Command is hard to remember，hard to use.

[skyformat99]

@jake2009
yes! support. I expect to see CockroachDB will compatible to a powerful ui tool . https://github.com/sosedoff/pgweb But I tried to use it, I connected CockroachDB, but I couldn't connected .

[dianasaur323]

@jake2009 and @skyformat99 thank you for being users of CockroachDB, and for the feedback! For now, I would suggest trying Postico. We use it internally for (albeit) relatively simple queries, and it has worked for us so far. Hopefully we will be able to do better soon, but unfortunately it isn't on our 1.2 roadmap quite yet.

[bladefist]

@dianasaur323 postico is mac only. Not a ton of DB Admins using mac I bet.

[dianasaur323]

point taken - see some of the discussion in this related issue. https://github.com/cockroachdb/cockroach/issues/15441#issuecomment-331562544

We will work on finding a better solution with our next release (1.2).

[cuongdo]

Reassigned to @awoods187 for prioritization

[awoods187]

@jordanlewis is this still blocked after the other work you did?

[justinj]

@awoods187 This is still blocked by correlated subquery support.

[knz]

sorry wrong click

[jordanlewis]

This looks like it's mostly working now!

We're missing information_schema.columns.udt_name, which throws an error, but everything else seems to pretty much work. There's a query we can't decorrelate, but it doesn't stop NaviCat from working.

I180905 20:57:16.667506 445 sql/conn_executor.go:1052  [n1,client=[::1]:49741,user=root] [NoTxn pos:22] executing ExecStmt: SELECT attname AS name, attrelid AS tid, COALESCE((SELECT attnum = ANY (conkey) FROM pg_constraint WHERE (contype = 'p') AND (conrelid = attrelid)), false) AS primarykey, NOT (attnotnull) AS allownull, (SELECT seq.oid FROM pg_class AS seq LEFT JOIN pg_depend AS dep ON seq.oid = dep.objid WHERE ((seq.relkind = 'S'::CHAR) AND (dep.refobjsubid = attnum)) AND (dep.refobjid = attrelid)) IS NOT NULL AS autoincrement FROM pg_attribute WHERE ((attisdropped = false) AND (attrelid = (SELECT tbl.oid FROM pg_class AS tbl LEFT JOIN pg_namespace AS sch ON tbl.relnamespace = sch.oid WHERE ((tbl.relkind = 'r'::"char") AND (tbl.relname = 'a')) AND (sch.nspname = 'public')))) AND (attname = 'a')
I180905 20:57:16.667567 445 sql/conn_executor.go:1052  [n1,client=[::1]:49741,user=root] [Open pos:22] executing ExecStmt: SELECT attname AS name, attrelid AS tid, COALESCE((SELECT attnum = ANY (conkey) FROM pg_constraint WHERE (contype = 'p') AND (conrelid = attrelid)), false) AS primarykey, NOT (attnotnull) AS allownull, (SELECT seq.oid FROM pg_class AS seq LEFT JOIN pg_depend AS dep ON seq.oid = dep.objid WHERE ((seq.relkind = 'S'::CHAR) AND (dep.refobjsubid = attnum)) AND (dep.refobjid = attrelid)) IS NOT NULL AS autoincrement FROM pg_attribute WHERE ((attisdropped = false) AND (attrelid = (SELECT tbl.oid FROM pg_class AS tbl LEFT JOIN pg_namespace AS sch ON tbl.relnamespace = sch.oid WHERE ((tbl.relkind = 'r'::"char") AND (tbl.relname = 'a')) AND (sch.nspname = 'public')))) AND (attname = 'a')
I180905 20:57:16.668450 445 sql/conn_executor.go:1225  [n1,client=[::1]:49741,user=root] execution error: could not decorrelate subquery
I180905 20:57:16.668509 445 sql/conn_executor.go:1052  [n1,client=[::1]:49741,user=root] [NoTxn pos:23] executing Sync
I180905 20:57:16.669499 445 sql/conn_executor.go:1052  [n1,client=[::1]:49741,user=root] [NoTxn pos:24] executing ExecStmt: SELECT cl.column_name, ty.typtype, cl.udt_schema, ty.typname, cl.column_default FROM information_schema.columns AS cl LEFT JOIN pg_type AS ty ON ty.typname = cl.udt_name LEFT JOIN pg_namespace AS nsp ON nsp.oid = ty.typnamespace WHERE ((nsp.nspname = cl.udt_schema) AND (cl.table_schema = 'public')) AND (cl.table_name = 'a')
I180905 20:57:16.669571 445 sql/conn_executor.go:1052  [n1,client=[::1]:49741,user=root] [Open pos:24] executing ExecStmt: SELECT cl.column_name, ty.typtype, cl.udt_schema, ty.typname, cl.column_default FROM information_schema.columns AS cl LEFT JOIN pg_type AS ty ON ty.typname = cl.udt_name LEFT JOIN pg_namespace AS nsp ON nsp.oid = ty.typnamespace WHERE ((nsp.nspname = cl.udt_schema) AND (cl.table_schema = 'public')) AND (cl.table_name = 'a')
I180905 20:57:16.669721 445 sql/conn_executor.go:1225  [n1,client=[::1]:49741,user=root] execution error: column "cl.udt_name" does not exist```

[awoods187]

cc @andy-kimball for the query we can't decorrelate

[andy-kimball]

I took a look at the query, and don't see a good way to decorrelate it (creates Max1Row operators, creates projections that get in the way of decorrelation, etc.). This looks like a good justification case for general purpose Apply.

[awoods187]

@andy-kimball can we handle this query now?

[andy-kimball]

I believe it works in terms of the apply-join issue. However, on one of the queries I did get an resolution error because the udt_schema does not exist in the CRDB information_schema.columns table. So we're past the first blocker issue, but there may be others.

[jordanlewis]

Navicat now appears to be working well.