We recently started adding support for CTEs in Vitess! You can check out https://github.com/vitessio/vitess/pull/14321 if you want to see some technical details of the implementation.
For now, we have added preliminary support by converting them to derived tables internally, but we believe that we need to make CTEs first-class citizens themselves of query planning, specifically because recursive CTEs are very hard to model as derived tables. Once we make that change, we can look towards supporting recursive CTEs.
This however will take some time, but then, all good things do!
So how do you deal with orphaned child rows when reverting? I assume it's up to your users to deal with them or not?
This very much seems like a clever automation for chosing when to care about foreign key constraints and not outright enforcement
Yes, you got that right! If you drop a foreign key constraint from a child table, and then follow up to INSERT/DELETE rows on parent and child in such way that is incompatible with foreign key constraints, and then revert, then the child, now again with the foreign key constraint, can have orphaned rows. It's as if you did `SET FOREIGN_KEY_CHECKS=0` and manipulated the data unobstructed. The schema itself remains valid, and some rows do not comply.
It's worth noting that MySQL has no problem with this kind of situation. It never cares about the existence of orphaned rows. It only cares about not letting you creating them in the first place, and it cares about cleaning up. But it doesn't blow up if orphaned rows do exist. They will just become ghosts.
Coming from a PostgreSQL worldview, I find this confusing. To me a foreign key constraint is about the referential integrity of a table, not an insert-time rule that can have loopholes to leave invalid data in the table. If the constraint is in place, I should be able to trust that any queryable data satisfies the constraint.
Also from my PostgreSQL-infused worldview, it seems to me you are making your life too difficult by requiring a migration to make "one change" to a table or view. The brute force idiom I've seen for schema migrations is to break it into phases:
1. drop departing foreign key constraints
2. restructure table columns/types and values
3. add new foreign key constraints
This is a bit like running with constraints deferred while making data changes that might look invalid until all data changes are done. But, it defers expression of the new constraints until the table structures are in place to support their definitions too, so it isn't just about deferring enforcement.
The same strategy can be used for import scenarios to support schemas where there are circular foreign key reference constraints. I.e. tables are not in a strict parent-child hierarchy.
Valid points! In my experience, when someone has foreign key constraints in their database, they tend to develop their apps in "trusting" way. Meaning, the app trusts the DB to maintain referential integrity. When you do the three step breakdown, you remove that integrity, and the app doesn't know any better: it keeps feeding the database with data, the database says "fine", and the app assumes referential integrity is preserved.
This is why in our design PlanetScale will not take upon itself to do this three step change. The user is more than welcome to break this into three different (likely they'll be able to make it in just two) schema changes. But then the user takes ownership of handling unchecked references.
> making data changes that might look invalid until all data changes are done
In effect, the data _will be_ invalid, and potentially for many hours.
Now, it's true that if the user messed up the data in between, then adding the foreign key constraint will fail, in both PostgreSQL and in MySQL. To me, this signals more bad news, because now the user has to scramble to clean up whatever incorrect data they have, before they're able to complete their schema change and unblock anyone else who might be interested in modifying the table.
Personally, my take is to not use foreign key constraints on large scale databases. It's nice to have, but comes at a great cost. IMHO referential data integrity should be handled, gracefully, by the app. Moreover, referential integrity is but one aspect of data integrity/consistency. There are many other forms of data integrity, which are commonly managed by the app, due to specific business logic. I think the app should own the data as much as it can. My 2c.
Ah, in my worldview those steps are still in a single transaction. It's just formulated as several ordered statements.
Is that mechanism unique to PostgreSQL? Or is it just that this transaction is impractical for you, due to the wall clock duration and how it impacts other use of the DB?
I have a rule not to revert migrations in production. If things have gone wrong enough that you need to rollback then there are all sorts of ways that trying to go back again could be bad news.
My main worry is that I’d end up with 3 classes of data; pre migration, changed / added post migration and post revert. It’s probably fine in most cases, but that could take quite some unpicking.
We've all been there and have been hit hard by rolling back data as well as by not rolling back data. What we do with PlanetScale Reverts, though, is to preserve your data through the rollback. There aren't three classes of data, just one (or, it's nuanced, let's call it 1.5).
As you complete the migration, PlanetScale keeps your old table, and continues to sync any further incoming changes to the table, back to the old table. They're kept in sync, apart of course from what incompatible schema changes they may have. As you revert, we flip the two, placing your old table back, but now not only with the old data, but also with all the newly accumulated data.
I completely appreciate the roll-forward approach. That's something we do for code deployments. Except when we don't, when there's that particular change where the best approach is to revert a commit, revert a PR. I think of schema deployments in the same way. Reverts will be rare, hopefully, but they can save the day.
