Prevent accidental whole-table DELETEs and UPDATEs

From: Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Prevent accidental whole-table DELETEs and UPDATEs
Date: 2023-02-02 20:21:39
Message-ID: CANNMO++s0ayutEaM8qu+MDzPOAvWnc8uJSfBSWCRh+MKZoBjNw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In many cases, a DELETE or UPDATE not having a WHERE clause (or having it
with a condition matching all rows in the table) is a sign of some kind of
mistake, leading to accidental data loss, performance issues, producing a
lot of dead tuples, and so on. Recently, this topic was again discussed [1]

Attached is a patch implemented by Andrey Boroding (attached) during our
today's online session [2], containing a rough prototype for two new GUCs:

- prevent_unqualified_deletes
- prevent_unqualified_updates

Both are "false" by default; for superusers, they are not applied.

There is also another implementation of this idea, in the form of an
extension [3], but I think having this in the core would be beneficial to
many users.

Looking forward to your feedback.

[1] https://news.ycombinator.com/item?id=34560332
[2] https://www.youtube.com/watch?v=samLkrC5xQA
[3] https://github.com/eradman/pg-safeupdate

Attachment Content-Type Size
0001-Add-GUCs-to-preven-some-accidental-massive-DML.patch application/octet-stream 3.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2023-02-02 20:22:55 Re: run pgindent on a regular basis / scripted manner
Previous Message Peter Geoghegan 2023-02-02 20:15:32 Re: Amcheck verification of GiST and GIN