Module Monday: safeupdate

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...