3 ms·

Because diffs don't work. If you rename a table, for instance, a diff algorithm will see that as dropping the table and creating a new one.

by ben509 7y ago

djrobstep 7y ago
Which is straightforwardly solved by editing the generated script accordingly.
- skore 7y ago
  Huh, this got me thinking. How about you just give a UUID to everything in the schema? The technical challenge with generated scripts (that you could edit by hand, but that just means that you now don't have an automated system) is that they don't understand changes at a deep enough level - they lack the context to see that a table or column has been renamed because they have no understanding of identity. So - just give them identity. Schema at commit 20b1ea23 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 25233812-9a95-4bc3-893e-6accb935fa49 name 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address 83fc34c8-56c7-49d4-94d0-150cd76204bc password Schema at commit c0d07562 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password Now you could diff these two: @@ -1,4 +1,6 @@ 03496418-e44c-42a6-a6a4-6563b7ae7bfb users - 25233812-9a95-4bc3-893e-6accb935fa49 name + 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname + 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address + c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password and every line of the diff has the necessary information to decide what operation you intended.
evanelias 7y ago
People love to cite this reason, but practically speaking, it's FUD. Renaming in production -- whether entire tables or just a column -- is operationally complex no matter what. Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. So either you end up with user-facing errors in the interim, or you can try to write application logic that can interact with both old and new names simultaneously. That's overly complex, typically not supported by ORMS or DAOs, and very risky in terms of bug potential anyway. I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. Either way, lack of rename support in diff-based schema management isn't really a problem, as long as the tooling has these two properties: 1. Catches unsafe/destructive changes in general and require special confirmation for them (preventing accidental rename-as-drop in the rare case where a rename truly is desired) 2. Offers a way to "pull" from the db side, so that if a rename is actually needed, it can be done "out of band" / manually, and the schema repo can still be updated anyway
- ben509 7y ago
  > People love to cite this reason, but practically speaking, it's FUD. > Either way, lack of rename support in diff-based schema management You're trying to polish a turd here, this isn't "lack of support" it's "it will drop objects in production." > Catches unsafe/destructive changes in general and require special confirmation for them Again, polishing a turd: the only way your "automation" works is through manual intervention. > Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. You can construct a view referencing the old table, and rename the table. Yes, it has to be an updateable view and you need transactional DDL, but within those constraints, it's doable. > I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. If they're using a DBMS that doesn't support transactional DDL, completely understandable. If their tools are liable to drop production data due to renames, also completely understandable. But the fact that they ban a trivial operation is a symptom of the problem with all the half solutions and snake oil in SQL schema management. It's so bad that you have large companies investing heavily in ripping out the schema entirely, which itself is just more snake oil. In the problem space of trying to keep a schema in sync, we know that diffing leads to unacceptable answers, that is an indicator that it's the wrong conceptual basis for a correct solution.
  - evanelias 7y ago
    > You're trying to polish a turd here, this isn't "lack of support" it's "it will drop objects in production." That's a strawman argument. Any reasonable schema management implementation has safeguards against DROPs. If your tooling blindly executes a DROP without extra confirmation, use better tooling. > Again, polishing a turd: the only way your "automation" works is through manual intervention. There's absolutely nothing wrong with requiring extra human confirmation for destructive actions. Quite the contrary. I've spent most of the last decade working on database automation and operations at social network scale, and will happily say this is a common practice, and it's a good one at that. > You can construct a view referencing the old table, and rename the table. Yes, it has to be an updateable view and you need transactional DDL, but within those constraints, it's doable. So you're assuming that every single table has a view in front; or you're dynamically replacing the table with a view and hoping that has no detrimental impact to other database objects or app performance. Either way, you're talking about something operationally complex enough that it isn't fair to say that production table renames or column renames are a "trivial operation" at the vast majority of companies. > It's so bad that you have large companies investing heavily in ripping out the schema entirely, which itself is just more snake oil. This is frequently overstated. For example, although Facebook uses a flexible solution for its largest sharded tables, there are many tens of thousands of other tables at Facebook using traditional schemas. > In the problem space of trying to keep a schema in sync, we know that diffing leads to unacceptable answers, that is an indicator that it's the wrong conceptual basis for a correct solution. The only "unacceptable answer" you've cited is rename scenarios, which even if it incorrectly leads to a DROP, the tooling will catch. If you need crazy view-swapping magic to support an operation (renames), that is an indicator that it's a conceptually problematic operation that should be strongly reconsidered in production. As I've already stated elsewhere in this thread, declarative schema management has been successfully used company-wide by Facebook by nearly a decade, and is also a common practice in the MS SQL Server world. If you're unconvinced, that's fine, but many companies have found it to be a great workflow!

Because diffs don't work. If you rename a table, for instance, a diff algorithm will see that as dropping the table and creating a new one.

by ben509 7y ago

Because diffs don't work. If you rename a table, for instance, a diff algorithm will see that as dropping the table and creating a new one.

djrobstep 7y ago

Which is straightforwardly solved by editing the generated script accordingly.

skore 7y ago
Huh, this got me thinking. How about you just give a UUID to everything in the schema? The technical challenge with generated scripts (that you could edit by hand, but that just means that you now don't have an automated system) is that they don't understand changes at a deep enough level - they lack the context to see that a table or column has been renamed because they have no understanding of identity. So - just give them identity. Schema at commit 20b1ea23 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 25233812-9a95-4bc3-893e-6accb935fa49 name 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address 83fc34c8-56c7-49d4-94d0-150cd76204bc password Schema at commit c0d07562 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password Now you could diff these two: @@ -1,4 +1,6 @@ 03496418-e44c-42a6-a6a4-6563b7ae7bfb users - 25233812-9a95-4bc3-893e-6accb935fa49 name + 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname + 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address + c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password and every line of the diff has the necessary information to decide what operation you intended.

evanelias 7y ago

People love to cite this reason, but practically speaking, it's FUD. Renaming in production -- whether entire tables or just a column -- is operationally complex no matter what. Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. So either you end up with user-facing errors in the interim, or you can try to write application logic that can interact with both old and new names simultaneously. That's overly complex, typically not supported by ORMS or DAOs, and very risky in terms of bug potential anyway. I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. Either way, lack of rename support in diff-based schema management isn't really a problem, as long as the tooling has these two properties: 1. Catches unsafe/destructive changes in general and require special confirmation for them (preventing accidental rename-as-drop in the rare case where a rename truly is desired) 2. Offers a way to "pull" from the db side, so that if a rename is actually needed, it can be done "out of band" / manually, and the schema repo can still be updated anyway

ben509 7y ago
> People love to cite this reason, but practically speaking, it's FUD. > Either way, lack of rename support in diff-based schema management You're trying to polish a turd here, this isn't "lack of support" it's "it will drop objects in production." > Catches unsafe/destructive changes in general and require special confirmation for them Again, polishing a turd: the only way your "automation" works is through manual intervention. > Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. You can construct a view referencing the old table, and rename the table. Yes, it has to be an updateable view and you need transactional DDL, but within those constraints, it's doable. > I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. If they're using a DBMS that doesn't support transactional DDL, completely understandable. If their tools are liable to drop production data due to renames, also completely understandable. But the fact that they ban a trivial operation is a symptom of the problem with all the half solutions and snake oil in SQL schema management. It's so bad that you have large companies investing heavily in ripping out the schema entirely, which itself is just more snake oil. In the problem space of trying to keep a schema in sync, we know that diffing leads to unacceptable answers, that is an indicator that it's the wrong conceptual basis for a correct solution.
- evanelias 7y ago
  > You're trying to polish a turd here, this isn't "lack of support" it's "it will drop objects in production." That's a strawman argument. Any reasonable schema management implementation has safeguards against DROPs. If your tooling blindly executes a DROP without extra confirmation, use better tooling. > Again, polishing a turd: the only way your "automation" works is through manual intervention. There's absolutely nothing wrong with requiring extra human confirmation for destructive actions. Quite the contrary. I've spent most of the last decade working on database automation and operations at social network scale, and will happily say this is a common practice, and it's a good one at that. > You can construct a view referencing the old table, and rename the table. Yes, it has to be an updateable view and you need transactional DDL, but within those constraints, it's doable. So you're assuming that every single table has a view in front; or you're dynamically replacing the table with a view and hoping that has no detrimental impact to other database objects or app performance. Either way, you're talking about something operationally complex enough that it isn't fair to say that production table renames or column renames are a "trivial operation" at the vast majority of companies. > It's so bad that you have large companies investing heavily in ripping out the schema entirely, which itself is just more snake oil. This is frequently overstated. For example, although Facebook uses a flexible solution for its largest sharded tables, there are many tens of thousands of other tables at Facebook using traditional schemas. > In the problem space of trying to keep a schema in sync, we know that diffing leads to unacceptable answers, that is an indicator that it's the wrong conceptual basis for a correct solution. The only "unacceptable answer" you've cited is rename scenarios, which even if it incorrectly leads to a DROP, the tooling will catch. If you need crazy view-swapping magic to support an operation (renames), that is an indicator that it's a conceptually problematic operation that should be strongly reconsidered in production. As I've already stated elsewhere in this thread, declarative schema management has been successfully used company-wide by Facebook by nearly a decade, and is also a common practice in the MS SQL Server world. If you're unconvinced, that's fine, but many companies have found it to be a great workflow!