4 ms·
One of the most annoying things for me about Postgres when I switched from MySQL is that in the former you can't put LIMIT clauses on UPDATE/DELETE statements.
by d_burfoot 10y ago
One of the most annoying things for me about Postgres when I switched from MySQL is that in the former you can't put LIMIT clauses on UPDATE/DELETE statements.
When I'm doing surgical work on a DB, I always, always want to do something like :
- SELECT statement showing me the relevant rows (and rowcount=N)
- UPDATE/DELETE statement with a corresponding limit equal to N+1
It gives nice, easy peace of mind that you aren't accidentally wiping out your whole table because you messed up the logic on your WHERE clause. Of course if you made a mistake, you probably still killed N records, but often that is much much better than killing the whole table (and maybe also downstream tables that are linked with FKEY cascade delete!)
- mastazi 10y agoIt seems to me that the amount of work required to fix the above, in most cases, would be almost equal to the amount of work that would be required if you hadn't used the LIMIT clause.
- saurik 10y agoIf you are that concerned, put "begin;" before your command, check that the updated/inserted number is reasonable, and then either do a commit or a rollback. That way you also didn't screw up N+1 totally random rows. You can even put a returning clause on the update to see which rows you were affecting.
- piaste 10y agoOr if you can't afford to lock the table in the process, before you run UPDATE {foo} WHERE {bar} you back up the relevant rows into a temporary table with SELECT * INTO foo_backup WHERE {bar}.
- saurik 10y agoNone of this should cause a table lock: at best you will just lock the affected rows and at worst you will end up with a predicate lock on the where clause.
- scardine 10y agoPostgres uses MVCC (Multiversion concurrency control), it solves concurrency without locking everything you touch.