3 ms·
Post author here, happy to answer technical questions.
by shlomi-noach 3y ago
Post author here, happy to answer technical questions.
- guptamanan100 3y agoI am the other post-author, and I am available too.
- padre24 3y agoThis was a great read, thanks. Are there any plans to support recursive CTEs? What are the technical challenges there?
- guptamanan100 3y agoThank you for the compliment! We recently started adding support for CTEs in Vitess! You can check out https://github.com/vitessio/vitess/pull/14321 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!
- padre24 3y agoAwesome news! We work with hierarchical data so it was a non starter for us.
- derekperkins 3y agoIf you don't need recursive CTEs on sharded databases, they'll work today. We're actively using them
- guptamanan100 3y agoOh, I see, that's unfortunate! Hopefully, we can remedy that though as soon as possible.
- jtriangle 3y agoSo 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
- shlomi-noach 3y agoYes, 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.
- saltcured 3y agoComing 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.