Re: DELETE with LIMIT (or my first hack)

On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro <loureirorg(at)gmail(dot)com> wrote:
> frequently i have accidents with DELETE/UPDATE commands. In fact, sometimes
> in the last 8 or 9 years (ok, a lot of times) I forget the entire WHERE
> clause or have a “not so perfectly“ WHERE clause, with an awful suprise.
> There’s no words to figure the horror ever time i see that the number of
> affected rows its not 1 or two how expected, but the entire table. So I
> planned to make a hack to make the “LIMIT” directive available to “DELETE”
> command.
>
> So, can anyone help-me in how to do this ? This its my plan: 1) change the
> lex grammar (wheres the file ?) 2) change the parser to accept the new
> grammar 3) change the executor to stop after “n” successful iterations. Is
> this correct ?

I don't think your use case sounds very compelling - as Jaime says,
you could still easily blow away data that you have no easy way to get
back - but I agree that DELETE (or UPDATE) is useful in combination
with LIMIT. For example, suppose you want to roll your own
replication solution for a table with no primary key. So you set up
some triggers. Whenever you see an INSERT on the source table, you do
a matching INSERT on the target table. When you see a DELETE on the
source table, you do a DELETE on the target table that constrains all
the columns to be equal and also includes LIMIT 1. Similarly for
UPDATE. Then, your boss gives you a big raise and commends you for
your awesome programming skills. Woot!

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company