What do you think about the TRUNCATE ... CASCADE functionality in terms of UX? The docs clearly state what the behavior is [1], but I've worked on managed Postgres and Postgres tooling for over fifteen years now, and I can't say with certainty I would not have made the same mistake [2]. Do you think there's reasonable changes Postgres could make to the syntax or the mechanism around TRUNCATE to avoid accidents like this?
[1]: https://www.postgresql.org/docs/current/sql-truncate.html
[2]: I like to think I would have at least checked the docs because there's no efficient way for TRUNCATE ... CASCADE to work how you intended given how I know TRUNCATE is implemented, but who knows?
I wasn't personally aware of the migration until after it had been executed, but if I'd reviewed the PR I'm not confident I would have known any better. It doesn't feel intuitive that any table with any foreign keys gets completely wiped.
Ouch. DELETE FROM <new_table> is what they wanted here.
Somewhat related, and this wouldn't have saved them since they were using TRUNCATE instead of DELETE, but I always use ON DELETE RESTRICT instead of ON DELETE CASCADE when setting up foreign keys. I'd rather be slightly inconvenienced on the rare occasion I'm hard-deleting data and have to be really explicit about what I want to delete rather than inadvertently allow a "convenience" like ON DELETE CASCADE to delete my data for me.
4 comments
[ 0.20 ms ] story [ 30.2 ms ] thread[1]: https://www.postgresql.org/docs/current/sql-truncate.html [2]: I like to think I would have at least checked the docs because there's no efficient way for TRUNCATE ... CASCADE to work how you intended given how I know TRUNCATE is implemented, but who knows?
Ouch. DELETE FROM <new_table> is what they wanted here.
Somewhat related, and this wouldn't have saved them since they were using TRUNCATE instead of DELETE, but I always use ON DELETE RESTRICT instead of ON DELETE CASCADE when setting up foreign keys. I'd rather be slightly inconvenienced on the rare occasion I'm hard-deleting data and have to be really explicit about what I want to delete rather than inadvertently allow a "convenience" like ON DELETE CASCADE to delete my data for me.