Foreign key and postgreSQL compatibility

If you create a table with a foreign key it isn’t available to tools that would normally be able to interrogate the link to determine relationships e.g.

  1. DBeaver schema diagrams or Foreign Key lists.
  2. SQLAlchemy (python) - can’t auto join through foreign keys. Would expect to write:
    query(Title.decription).join(User).filter(User.email == 'ann.other@my.org')
    But need to write:
    query(Title.decription).join(User, Title.id == User.id_title).filter(User.email == 'ann.other@my.org')

I initially discovered this was that SQLAlchemy doesn’t even include the foreign keys in the table DDL under the CDB dialect (issue raised) but applying DDL generated for postgreSQL and running that on CDB does result in enforced foreign keys as we would expect.

Under DBeaver the error raised when attempting to list foreign keys is:

SQL Error [XX000]: ERROR: syntax error at or near "."
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,   (i.keys).n AS KEY_SEQ, ci.relname AS PK_NAME FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid)   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)   JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary,              information_schema._pg_expandarray(i.indkey) AS keys         FROM pg_catalog.pg_index i) i     ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE true  AND n.nspname = 'caas' AND ct.relname = 'title_dict' AND i.indisprimary  ORDER BY table_name, pk_name, key_seq
                                                                                                                    ^

Can anyone comment on plans to improve compatibility to this level or are these unlikely to be solved?

Thanks,
Keiran

Hi @kr2,

Thanks for the report. There are a few different things in this query that we don’t support, unfortunately.

  1. pg_expandarray - that’s tracked here: https://github.com/cockroachdb/cockroach/issues/16971
  2. dot-notation access to outputs. That’s not tracked yet but we’ve thought a little bit about it.

We do plan to support these things eventually.