A recent tweet pointed me to
safeupdate which is a little extension that forbids unconditional
UPDATE and
DELETE statements, i.e. without
WHERE clause.
Let's see how this works...
The build and install procedure is pretty non-spectacular:
make,
make install and then either activate it per session with
load 'safeupdate' or globally by adding it to
postgresql.conf:
shared_preload_libraries = 'safeupdate'
After that, if you try an unconditional UPDATE or DELETE on any table, you'll get the following errors:
ERROR: UPDATE requires a WHERE clause
and
ERROR: DELETE requires a WHERE clause
respectively and the operation is cancelled.
Unconditional
UPDATE and
DELETE can still be forced by using a condition that always evaluates to
true, like
WHERE TRUE or
WHERE 1=1
But now you have to explicitly
request this behaviour.
I think this is ever so useful to protect against human error, that I wonder why this isn't something mandatory, even if it's non-standard.
However, there is one glitch: If you
JOIN tables to
UPDATE or
DELETE only certain rows that match the
ON condition, this is not recognized as a conditional operation and the
WHERE TRUE is still needed to pacify the extension. Well, I can live with that...
