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
Chris Campbell <chris@bignerdranch.com> 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 > 10000000
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
On Thu, Jun 15, 2006 at 10:35:19PM -0400, Tom Lane wrote:
Chris Campbell <chris@bignerdranch.com> 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 > 10000000
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
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | 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...
Chris Campbell <chris@bignerdranch.com> 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, egDELETE FROM tab WHERE key > 1
where you meant to type
DELETE FROM tab WHERE key > 10000000
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?
On 6/16/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
Chris Campbell <chris@bignerdranch.com> 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, egDELETE FROM tab WHERE key > 1
where you meant to type
DELETE FROM tab WHERE key > 10000000
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
On 6/16/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
Chris Campbell <chris@bignerdranch.com> writes:
I heard an interesting feature request today: preventing the
execution of a DELETE or UPDATE query that does not have a WHEREclause.
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, egDELETE FROM tab WHERE key > 1
where you meant to type
DELETE FROM tab WHERE key > 10000000
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.