Re: [HACKERS] Preventing DELETE and UPDATE without a WHERE clause?
> On 6/16/06, Mark Woodward <[EMAIL PROTECTED]> wrote: >> > Chris Campbell <[EMAIL PROTECTED]> writes: >> >> I heard an interesting feature request today: preventing the >> >> execution of a DELETE or UPDATE query that does not have a WHERE >> clause. >> > >> > These syntaxes are required by the SQL spec. Furthermore, it's easy >> > to imagine far-more-probable cases in which the system wouldn't detect >> > that you'd made a mistake, eg >> > >> > DELETE FROM tab WHERE key > 1 >> > >> > where you meant to type >> > >> > DELETE FROM tab WHERE key > 1000 >> > >> > I suggest counseling your client to learn how to use BEGIN/ROLLBACK. >> > This proposal strikes me as falling squarely within the rule about >> > "design a system that even a fool can use, and only a fool will want >> > to use it". >> > >> Just a theory, couldn't a trigger be set up that would case the query to >> tank if it touches too many rows? >> > > i haven't tried but maybe a FOR STATEMENT trigger AFTER the event can > ask ROW_COUNT using GET DIAGNOSTICS? Well, if you *can't" do it in a trigger, maybe that's a valid modification for Hackers to consider. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Preventing DELETE and UPDATE without a WHERE clause?
On 6/16/06, Mark Woodward <[EMAIL PROTECTED]> wrote: > Chris Campbell <[EMAIL PROTECTED]> writes: >> I heard an interesting feature request today: preventing the >> execution of a DELETE or UPDATE query that does not have a WHERE clause. > > These syntaxes are required by the SQL spec. Furthermore, it's easy > to imagine far-more-probable cases in which the system wouldn't detect > that you'd made a mistake, eg > > DELETE FROM tab WHERE key > 1 > > where you meant to type > > DELETE FROM tab WHERE key > 1000 > > I suggest counseling your client to learn how to use BEGIN/ROLLBACK. > This proposal strikes me as falling squarely within the rule about > "design a system that even a fool can use, and only a fool will want > to use it". > Just a theory, couldn't a trigger be set up that would case the query to tank if it touches too many rows? i haven't tried but maybe a FOR STATEMENT trigger AFTER the event can ask ROW_COUNT using GET DIAGNOSTICS? -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Preventing DELETE and UPDATE without a WHERE clause?
> Chris Campbell <[EMAIL PROTECTED]> writes: >> I heard an interesting feature request today: preventing the >> execution of a DELETE or UPDATE query that does not have a WHERE clause. > > These syntaxes are required by the SQL spec. Furthermore, it's easy > to imagine far-more-probable cases in which the system wouldn't detect > that you'd made a mistake, eg > > DELETE FROM tab WHERE key > 1 > > where you meant to type > > DELETE FROM tab WHERE key > 1000 > > I suggest counseling your client to learn how to use BEGIN/ROLLBACK. > This proposal strikes me as falling squarely within the rule about > "design a system that even a fool can use, and only a fool will want > to use it". > Just a theory, couldn't a trigger be set up that would case the query to tank if it touches too many rows? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Preventing DELETE and UPDATE without a WHERE clause?
On Thu, Jun 15, 2006 at 10:35:19PM -0400, Tom Lane wrote: > Chris Campbell <[EMAIL PROTECTED]> writes: > > I heard an interesting feature request today: preventing the > > execution of a DELETE or UPDATE query that does not have a WHERE clause. > These syntaxes are required by the SQL spec. Furthermore, it's easy > to imagine far-more-probable cases in which the system wouldn't detect > that you'd made a mistake, eg > DELETE FROM tab WHERE key > 1 > where you meant to type > DELETE FROM tab WHERE key > 1000 > I suggest counseling your client to learn how to use BEGIN/ROLLBACK. > This proposal strikes me as falling squarely within the rule about > "design a system that even a fool can use, and only a fool will want > to use it". What about a mode that would activate after 2am, and before 6am, that would prevent any delete or update operation that affects more than 50% of the rows? :-) Hehe. Only half serious... :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Preventing DELETE and UPDATE without a WHERE clause?
Chris Campbell <[EMAIL PROTECTED]> writes: > I heard an interesting feature request today: preventing the > execution of a DELETE or UPDATE query that does not have a WHERE clause. These syntaxes are required by the SQL spec. Furthermore, it's easy to imagine far-more-probable cases in which the system wouldn't detect that you'd made a mistake, eg DELETE FROM tab WHERE key > 1 where you meant to type DELETE FROM tab WHERE key > 1000 I suggest counseling your client to learn how to use BEGIN/ROLLBACK. This proposal strikes me as falling squarely within the rule about "design a system that even a fool can use, and only a fool will want to use it". regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Preventing DELETE and UPDATE without a WHERE clause?
I heard an interesting feature request today: preventing the execution of a DELETE or UPDATE query that does not have a WHERE clause. The user was worried about a typo leading to: DELETE FROM very_important_table and deleting all the data. Or doing something similar with an UPDATE: UPDATE very_important_table SET important_column = 'Smith' and all the rows now have their important_column set to Smith. I was thinking that this could be accomplished with a GUC to cause the server to report an error if DELETE and UPDATE queries don't contain WHERE clauses. "allow_mod_queries_without_qualifier" or something (which would obviously default to true). If this setting was activated (the GUC changed to false), the above queries could still be executed, but it would take a conscious effort by the user to add a WHERE clause: DELETE FROM very_important_table WHERE true; UPDATE very_important_table SET important_column = 'Smith' WHERE true; Would such a patch ever be accepted? Thanks! - Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
