Prevent accidental whole-table DELETEs and UPDATEs
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t47363psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 28, 2026 at 01:13 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t47363_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t47363_1 && git checkout t47363_1Patchset v1 (message #1) is on t47363_1
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]https://news.ycombinator.com/item?id=34560332
Attached is a patch implemented by Andrey Boroding (attached) during our
today's online session [2]https://www.youtube.com/watch?v=samLkrC5xQA, 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]https://github.com/eradman/pg-safeupdate, 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
Nikolay Samokhvalov <samokhvalov@gmail.com> writes:
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
This sort of thing has been proposed before and rejected before.
I do not think anything has changed. In any case, I seriously
doubt that something that's basically a one-line test (excluding
overhead such as GUC definitions) is going to meaningfully
improve users' lives. The cases that I actually see reported
are not "I left off the WHERE" but more like "I fat-fingered
a variable in a sub-select so that it's an outer reference,
causing the test to degenerate to WHERE x = x", or perhaps
"I misunderstood the behavior of NOT IN with nulls, ending up
with a constant-false or constant-true condition". I'm not sure
if there's a reliable way to spot those sorts of not-so-trivial
semantic errors ... but if we could, that'd be worth discussing.
regards, tom lane