On Tue, Nov 30, 2010 at 9:24 PM, Marko Tiikkaja <marko.tiikk...@cs.helsinki.fi> wrote: >> On 11/30/2010 02:12 PM, Kevin Grittner wrote: >>> >>> Daniel Loureiro<dan...@termasa.com.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers