Re: DELETE with LIMIT (or my first hack)

On Tue, Nov 30, 2010 at 9:24 PM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
>> On 11/30/2010 02:12 PM, Kevin Grittner wrote:
>>>
>>> Daniel Loureiro<daniel(at)termasa(dot)com(dot)br>   wrote:
>>>
>>>> to me the key its security - its a anti-DBA-with-lack-of-attention
>>>> feature.
>>>
>>> Well, it seems pretty weak to me for that purpose.  You still trash
>>> data, and you don't have any immediate clue as to what.
>>
>> I agree, that argument is completely misconceived. If the DBA is paying
>> enough attention to use LIMIT, s/he should be paying enough attention
>> not to do damage in the first place. If that were the only argument in
>> its favor I'd be completely against the feature.
>
> I don't buy the argument either; why would you put a LIMIT there and delete
> one row by accident when you could put a BEGIN; in front and not do any
> damage at all?
>
It is valuable as a DBA carelessness/typo catcher only if it is
imposed by default (in line with Kevin's point), and only if it rolls
back rather than reduces the number of affected rows (as per Marko).

We have implemented a damage limitation solution similar to this with
triggers on an MSSQL database, and it has worked for the specific
environment it's in. The safety net is basically that the DBA has to
set an environment variable before a very large delete or update
operation. If the operation is recognised as being beyond the
threshold size the enviroment variable is checked - if it is set the
transaction passes and the variable is reset, if not the transaction
is rolled back.

It should be possible to implement something along these lines in
triggers, all that would be needed is a structure for defining the
(optional) limits on potentially destructive operations. More flexible
options or options based on the number of rows in a table will rapidly
increase the performance impact of the triggers - but may make them
more useful.

I'm not sure if there is a way to persist data (like a row count)
between per row triggers so that the operation could be aborted at the
limit rather than only once all the rows had been updated (potentially
a big peformance gain).

Alastair "Bell" Turner

Technical Lead
^F5