Prevent accidental whole-table DELETEs and UPDATEs
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
Attachments:
0001-Add-GUCs-to-preven-some-accidental-massive-DML.patchapplication/octet-stream; name=0001-Add-GUCs-to-preven-some-accidental-massive-DML.patchDownload+45-2
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