News for hackers. Stuff that matters.
Re:Paranoid SQL Execution on Postgres by btown on Friday February 25, 2022 @06:45PM (#30473792)
#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.
Re:Paranoid SQL Execution on Postgres by mattashii on Friday February 25, 2022 @07:30PM (#30474144)
> 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.

Re:Paranoid SQL Execution on Postgres by orf on Friday February 25, 2022 @07:54PM (#30474304)
Absolutely run DDL in a transaction. There are some nuances, but as a general rule it's a great one.
Re:Paranoid SQL Execution on Postgres by traceroute66 on Saturday February 26, 2022 @06:06AM (#30476675)
> 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

Re:Paranoid SQL Execution on Postgres by DelightOne on Saturday February 26, 2022 @03:51AM (#30476166)
Are 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.

Re:Paranoid SQL Execution on Postgres by tpetry on Saturday February 26, 2022 @04:19AM (#30476284)
PostgreSQL has the RETURNING statement for DML queries. It‘s very effective for that case because you get all affected rows and their new values.
Re:Paranoid SQL Execution on Postgres by ctopher7 on Friday February 25, 2022 @08:25PM (#30474471)
why do you need to run SELECT with a transaction?