CockroachDB Support

[dezren39]

Hello! I like this library for it's ability to do either sql or go scripts, I'd like to use this tool with crdb. It is almost identical to postgres for these purposes, but transactions need a retry sometimes, so people often wrap pgx with this

https://pkg.go.dev/github.com/cockroachdb/cockroach-go/v2/crdb/crdbpgxv5 for the transaction part. There is a different one for pgxv4 and I'm sure other drivers have similar things. Is it possible to make a provider which is postgres except wrap TX in executetx?

[mfridman]

I don't have a good answer here, need to think about this one.

The entry point to goose right now is *sql.DB, and you can choose whatever driver you like, for Postgres it'll be lib/pq, pgx/v4, pgx/v5, etc.

You typically pair *sql.DB (with whatever driver you choose) with a dialect, so in this example DialectPostgres.

What this does is stub out a default database.Store (most of the time you don't need to know about this).

So, I wonder if there's some way for the user to implement the database.Store interface and internally within that implementation use retryable transactions with either v2/crdb/crdbpgxv5 or v2/crdb.

This is probably more detail than you asked for, but I'm thinking out loud about how we might approach this problem.

[dezren39]

This is probably more detail than you asked for, but I'm thinking out loud about how we might approach this problem.

I love it! I only barely understood most of this so far, but already have a bunch more things I can look up now. 😁Thank you! I'll try and keep track of this and maybe test some things out if I get time and figure enough of it out.

[mfridman]

I'm pretty sure I tested cockroachdb support ~yr ago, it should just work with a standard Postgres driver (I think I used pgx/v4 at the time).

To clarify, what you're looking for is to do retryable transactions on the client side? specifically this:

https://www.cockroachlabs.com/docs/v23.1/transactions#transaction-retries

https://www.cockroachlabs.com/docs/v23.1/transaction-retry-error-reference#client-side-retry-handling

https://www.cockroachlabs.com/docs/v23.1/advanced-client-side-transaction-retries

[dezren39]

Yeah basically! Maybe it's not a big issue, but I don't really know. I try to do migrations during low traffic without other activity, but circumstances can change. I figure I can use the provided library in any Go migrations I do, but then the SQL ones might still fail if I don't have some sort of cockroach provider. I'm still familiarizing myself with goose, so after I get a few more migrations handled maybe I'll be more useful. :-)

Another thought I had; was I can retry the migration from my own tooling if a transaction fails at least for now, probably a good idea to have retry logic at that level too anyways.