3 ms·
#0 should be: for anything other than DDL, run in a transaction. Immediately after executing, do sanity checks on data that you know should have been touched, a
by btown 5y ago
#0 should be: for anything other than DDL, run in a transaction. Immediately after executing, do sanity checks on data that you know should have been touched, and data you know shouldn’t have been touched. Ideally, you’re pair programming and your partner is the one doing those tests. Then you can rollback if you accidentally forgot a WHERE clause and destructively updated more than expected. Of course, every second you keep a transaction open contributes to bloat, so don’t be slow. But this has saved me countless times.
- mattashii 5y ago> other than DDL Especially, definately, also DDL (where possible). There are only few DDL statements that cannot be run transactionally (most of which is [CREATE / RE]INDEX CONCURRENTLY), but the rest of DDL can best be tested before committing to the result, especially when migrating between versioned schemas: you don't want half-applied migrations.
- orf 5y agoAbsolutely run DDL in a transaction. There are some nuances, but as a general rule it's a great one.
- ctopher7 5y agowhy do you need to run SELECT with a transaction?
- DelightOne 5y agoAre these runtime tests or only for CI/CD? How do you test what wasn't changed, how does that work. Do you select before and after and compare certain rows or tables and if so random ones or which ones? A small example would be awesome.
- tpetry 5y agoPostgreSQL has the RETURNING statement for DML queries. It‘s very effective for that case because you get all affected rows and their new values.
- traceroute66 5y ago> run in a transaction Or just use PL/pgSQL stored functions which are inherently transactions[1] No bad thing anyway as it has a side-benefit of helping the fight against SQL injection attacks etc. [1] https://www.postgresql.org/docs/14/plpgsql-transactions.html https://www.postgresql.org/docs/14/plpgsql-transactions.html