3 ms·
> * Enforce style rules with a linter/test, like "UPDATE must have a LIMIT" As someone who has never had access to a DB with any serious number of users, can y
by throwaway287391 7y ago
> * Enforce style rules with a linter/test, like "UPDATE must have a LIMIT"
As someone who has never had access to a DB with any serious number of users, can you explain this one further? What if you really do want to update every row? Do you just do LIMIT INT_MAX or the like, and just force people to write that so that they always know they're updating the entire table? Or are you saying you should only ever use UPDATE on a known finite (and small?) number of rows?
- groceryheist 7y agoI think it might just be like a psychological thing to try to prevent mistakes. Like a dialogue box asking "Sure?" before you delete your harddrive.
- londons_explore 7y agoUpdating every row is usually a bad idea anyway. That means every row needs to be rewritten, which involves reading and writing all data in the table. That will take locks which mean old data needs to be kept (in case of a transaction abort), doubling disk usage. Since half of rows are now dead, all other queries to the table will take double the disk access time. Basically, if your table is over a few gigabytes, updating every row in one query on a production instance is a really bad plan.
- CydeWeys 7y agoSo how do you make the change then? Do SQL dialects provide built-in tools to do an UPDATE to a large table in a saner way, or do you end up having to write an application to do so?
- joatmon-snoo 7y agoIt's the notion of a staggered rollout. You should never initiate a global change without first validating it on some small, controlled-impact subset.