Crdb support?

[dezren39]

I expect cockroachdb would probably work fine, because it uses pgx, but I wanted to ask if you had any thoughts about potential issues with using it with river. My main concern is that crdb requires transaction retry and so there are wrappers used with pgx for commit, see https://pkg.go.dev/github.com/cockroachdb/cockroach-go/crdb/crdbpgx and their ExecuteTx method. I would guess it should be fine, I can call ExecuteTx from within the job maybe, but I worry I'm missing obvious conflicts with how river may use transactions under the hood. I think there is good pairing between rivers goals and crdb, though, and so I'm hoping any potential conflicts are minor.

(pgx v5 version of that wrapper library https://pkg.go.dev/github.com/cockroachdb/cockroach-go/v2/crdb/crdbpgxv5 )

[bgentry]

I mentioned this briefly in #56 (comment)

In short I don’t think this is something we could support and I would bet even the Cockroach Labs folks would tell you it’s not a good idea. Top among the issues is that Cockroach often requires index hints to get reasonable query performance or to make use of an index, and because this isn’t a Postgres feature we can’t augment the queries to include such hints. But even if that were solved I think it’s pretty likely this use case would trigger some gnarly performance issues.

[dezren39]

That makes sense! Would this issue persist for any of these distributed types of things? Or could something like yugabyte eventually work OK? My understanding is it's more like regular postgres but I can imagine reasons that still won't be enough.

[danthegoodman1]

Another issue is the lack of LISTE/NOTIFY for leader election

[brandur]

Another issue is the lack of LISTE/NOTIFY for leader election

I think we'll have an answer for this one soon-ish at least. I've been working on a "poll only" mode for River that disables the LISTEN/NOTIFY infrastructure. It's actually done already, and just needs a little more testing from the client level and a way to turn it on.

[danthegoodman1]

That’s cool! I don’t know what other postgres-exclusive features might be in use though, I only mentioned that one because I stumbled across it in the docs and recalled that it’s not implemented in crdb (and probably won’t be tbh, issue was created in 2019)

[bgentry]

I will reiterate based on my past experience with Cockroach that even if we were to somehow get River to actually function with crdb it would probably still be a bad idea to try to use it in this way 🙂

[mfridman]

If someone unexpectedly finds this and thinks, "Hey, maybe... just maybe, this will work despite all the disclaimers," they will quickly run into an error during migrations. 😅

$ river migrate-up --database-url ...
Error: error applying version 002 [UP]: ERROR: unimplemented: arrays of JSON unsupported as column type (SQLSTATE 0A000)

Since CRDB doesn't support JSONB arrays. cockroachdb/cockroach#23468

[brandur]

Thanks @mfridman. Good to have the specific error on record.