Skip site navigation (1) Skip section navigation (2)

Re: DELETE with LIMIT (or my first hack)

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Daniel Loureiro <loureirorg(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: DELETE with LIMIT (or my first hack)
Date: 2010-11-30 03:11:47
Message-ID: AANLkTi=AuhQO_eG3zoPsnck-61G1GV5E2Zq6GsgQQYJA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Itagaki TakahiroDate: 2010-11-30 03:16:23
Subject: Re: pg_execute_from_file review
Previous:From: Jaime CasanovaDate: 2010-11-30 03:09:15
Subject: Re: DELETE with LIMIT (or my first hack)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group