Actually Cockroach doesn't support this
Actually Cockroach doesn't support this feature, which tbh makes support here potentially simpler. You could just treat malformed postgres versions as <15 and should be good to go.
I can throw together a PR if it's helpful
3 Replies
Hello! To the best of my knowledge we haven't tried pgroll with CockroachDB. Although according to the docs the Postgres wire protocol should be compatible https://www.cockroachlabs.com/docs/stable/postgresql-compatibility, there may be more adjustments required to make things work beyond the init step. I suggest filing an issue in the pgroll repo https://github.com/xataio/pgroll/issues/ for our dev team and the community involved with pgroll, to consider support for Cockroach.
GitHub
Issues · xataio/pgroll
PostgreSQL zero-downtime migrations made easy. Contribute to xataio/pgroll development by creating an account on GitHub.
Hi, since I wrote this I've been on a bit of a journey here lol.
CockroachDB has weird limits around what kinds and how many changes to schema (ALTER statements) it allows in a single transaction. I ended up dropping pgroll because it wasn't clear to me that it would necessarily play nice under cockroach's constraints.
Then we ended up switching to Aurora Postgres anyway, so I brought pgroll back. I've been really liking it but I'm starting to butt up against some limitations again.
My current pain points are:
1. Because operations in a migration can't depend on each other (i.e. can't create a table and an index in the same migration) I'm ending up with tons of migrations to add new features, which sort of breaks the backwards compatibility of the schema. I think this is largely a point-in-time thing since this is a greenfield app and I'm making huge changes that I expect to slow over time, but I could see this being annoying long-term.
2. Some feature in Postgres that don't seem to have out of the box support:
- Multi column constraints
-- Worked around by using a sql migration, would much prefer a built-in
- Foreign key ON DELETE CASCADE
-- I don't want to have to write indexes in raw sql when there's already a built-in for handling foreign keys. It would be really nice for right-to-be-forgotten requests to be able to delete all data by a specific unique ID automatically.
Hey @AA-morganhoke thanks a lot for sharing your feedback! This is very useful, we discussed with the dev team to check where we are about your points.
We agree operations depending on earlier operations in the same migration is a significant limitation. There is an issue about it in the pgroll repo here https://github.com/xataio/pgroll/issues/203#issuecomment-1818545668 and we're planning to work on it in the future.
Also, multi-column constraints are on our roadmap: https://github.com/xataio/pgroll/issues/81
As for the cascade delete, it's a feature request we've already heard about Xata, but not yet for pgroll itself. It would be great to log it in an issue in the pgroll repo. Let me know if you'd like to create one, otherwise we can do it.