How to Prevent Accidental Full Table Updates in PostgreSQL 18 Using pg-safeupdate

In production databases, a single incorrect SQL command can cause irreversible damage. This risk becomes even more serious in ERP systems like Odoo, where tables hold critical business data and transactional history.

One common mistake is running an UPDATE or DELETE query without a WHERE clause. This unintentionally modifies every row in the table.

PostgreSQL does not block such queries by default.

To solve this problem, we can use a lightweight extension called pg-safeupdate.

This article explains what pg-safeupdate does, why it is important in production environments, and how to install and use it on PostgreSQL 18, based on a real setup and test case.

What is pg-safeupdate?

pg-safeupdate is a PostgreSQL extension that prevents UPDATE and DELETE commands from executing unless they contain a WHERE clause.

If a query attempts to modify an entire table without filtering rows, PostgreSQL throws an error instead of executing it.

The extension is implemented in C and hooks directly into PostgreSQL’s execution layer. It introduces no measurable performance overhead and requires minimal configuration.

Its only purpose is safety.

Why pg-safeupdate is Important in Production

In real-world systems, especially Odoo databases:

• Tables often contain millions of records
• Manual SQL is frequently used for debugging or maintenance
• Mistakes happen during late-night operations
• Replication can instantly propagate bad changes
• Rollbacks may not always be possible

A single command like:

UPDATE res_users SET active = false;

can disable every user account.

pg-safeupdate acts as a guardrail. It forces engineers to be explicit about which rows they intend to modify.

This makes it extremely valuable for:

• Production environments
• ERP systems
• Manual DBA operations

Building pg-safeupdate from Source (PostgreSQL 18)

The extension was compiled directly from source.

Clone the repository

git clone https://github.com/eradman/pg-safeupdate.git --depth=1
cd pg-safeupdate

Directory contents:

LICENSE  Makefile  NEWS  README.md  safeupdate.c  test.rb

Build the extension

gmake

This produces the compiled file named safeupdate.so

Install the shared library

gmake install

Output:

/usr/bin/mkdir -p '/home/cybrosys/pg17/lib/postgresql'
/usr/bin/install -c -m 755 safeupdate.so '/home/cybrosys/pg17/lib/postgresql/'

Loading pg-safeupdate in PostgreSQL

Switch to postgres user and connect:

sudo su postgres
psql -p 5433

Load the module:

LOAD 'safeupdate';

Result:

LOAD

Enabling Using shared_preload_libraries

Check the path of the postgres config file

show config_file;

Result :

               config_file               
-----------------------------------------
 /etc/postgresql/18/main/postgresql.conf
(1 row)

Edit PostgreSQL configuration:

sudo nano /etc/postgresql/18/main/postgresql.conf

Add:

shared_preload_libraries = 'safeupdate'

Restart PostgreSQL:

sudo systemctl restart postgresql@18-main.service

Verify:

SHOW shared_preload_libraries;

Result:

safeupdate

Enable for a Specific Database

Instead of enabling globally, pg-safeupdate can be activated per database:

ALTER DATABASE odoo_production
SET session_preload_libraries = 'safeupdate';

Practical Example from an Odoo Database

Consider the Odoo table:

Table "public.ir_act_client"

Sample record:

SELECT * FROM ir_act_client;

Output:

id                 | 2
tag                | reload
name               | {"en_US": "Open Settings Menu"}

Attempting an Unsafe Update

The following command was executed:

UPDATE ir_act_client SET tag = 'load';

PostgreSQL responded like this

ERROR: UPDATE requires a WHERE clause

The extension successfully blocked a full-table update.

Without pg-safeupdate, this command would have modified every row.

The same thing for the delete query also

delete from ir_attachment ;

Result :

ERROR:  DELETE requires a WHERE clause

Correct Way to Perform the Update

The query must explicitly specify the target row:

UPDATE ir_act_client
SET tag = 'load'
WHERE id = 2;

Now the update executes as intended.

How pg-safeupdate Works Internally

pg-safeupdate hooks into PostgreSQL’s execution phase and inspects UPDATE and DELETE statements.

If no WHERE clause exists:

• The query is rejected
• No rows are modified

It does not affect:

• SELECT
• INSERT
• Queries containing WHERE

The logic is simple, but the protection it provides is significant.

Best Practices

For production:

• Always enable pg-safeupdate
• Especially for Odoo or ERP databases

For development:

• Prevent accidental mistakes
• Protect shared environments

pg-safeupdate should be considered a baseline safety tool.

pg-safeupdate is a small extension that provides a critical layer of protection for PostgreSQL.

It prevents accidental mass updates and deletions, forces explicit query intent, and integrates cleanly with PostgreSQL 18.

For Odoo-based systems and custom PostgreSQL deployments, this extension can prevent serious data loss caused by human error.

If you manage production databases, pg-safeupdate deserves a permanent place in your stack.