CockroachDB: infer_schema fails with Database Error (information_schema.referential_constraints does not exist)

[mmstick]

Getting this error when trying to load the database schema from CockroachDB with `infer_schema!("dotenv:DATABASE_URL"):

DatabaseError(__Unknown, "table \"information_schema.referential_constraints\" does not exist")

Any idea what would cause this? Trying to convert my website from using the postgres crate directly to this, so I know the dotenv that I'm using works for generating a connection pool.

[killercup]

Diesel doesn't officially support CockroachDB and we don't test against it. That said, from what I've heard, it generally works. Does cockroach support the full information schema standard? The referential_constraints table contains information on foreign keys and Diesel uses it to automatically make tables joinable. (This was recently added.) As a workaround, you can define the tables yourself with the `table!` macro and also manually call`joinable!`. Michael Murphy <notifications@github.com> schrieb am Do. 31. Aug. 2017 um 00:52:

…

Getting this error when trying to load the database schema from CockroachDB with `infer_schema!("dotenv:DATABASE_URL"): DatabaseError(__Unknown, "table \"information_schema.referential_constraints\" does not exist") Any idea what would cause this? Trying to convert my website from using the postgres crate directly to this, so I know the dotenv that I'm using works for generating a connection pool. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1134>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABOX8UGhzIskggRcvtfuxk_TvryvBeLks5sdeeRgaJpZM4PIFEy> .

[killercup]

Ah, looks like it doesn't support it yet. Their tracking issue for information_schema: cockroachdb/cockroach#8675

We can totally make this error nicer, though, and I'd love to see a PR for that :)

[tschottdorf]

Actually the issue to track here is probably cockroachdb/cockroach#13787 -- TL;DR it somewhat works, but a few workarounds are needed at the moment.

[nvanbenschoten]

This specific issue should be solved now.

That said, there are still a few other blockers listed in cockroachdb/cockroach#13787. CockroachDB has issues filled for each of the necessary changes: support for savepoints, the ability to drop and create the same table in a txn, and support for stored procedures. However, none of these are actually planned to be addressed in the near-future. I'm curious how many of these are hard requirements of Diesel and how many are artifacts of implementation decisions that could potentially be changed. For instance, it sounds like the stored procedures aren't actually strictly necessary. @killercup do you have an estimate for how much effort it would be to address each of these three limitations on Diesel's side?

[nvanbenschoten]

EDIT: it looks like the ability to drop and create the same table in a txn may actually be addressed sometime soon. The other's are still longer-term tasks though.

[sgrif]

The only thing that is a hard requirement for Diesel is support for savepoints.

[nvanbenschoten]

Got it, thanks @sgrif. From this line, it looks like Diesel requires support for arbitrarily nested savepoints. Is this correct?

I'll look into whether we can prioritize cockroachdb/cockroach#10735 on our end.

[sgrif]

Yes. Technically we only require it if you call transaction while already inside a transaction, so if you never do that it should be fine. That said, Diesel may open a transaction for certain methods (AFAIK we don't do this for PG currently, and I don't know that we'd ever really have a reason to, but who knows).

I guess the only other thing is modifying schema in a transaction. The reason that requirement exists is because we run migrations in a transaction. Again though, it's not a hard requirement, since using Diesel CLI to maintain your schema is not a requirement for using Diesel or the other features of Diesel CLI

FWIW MySQL just implicitly commits the transaction if you try to modify schema. I hate that behavior with a burning passion, but it may work as a shorter term solution.