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

Re: DELETE with LIMIT (or my first hack)

From: Rob Wultsch <wultsch(at)gmail(dot)com>
To: Daniel Loureiro <loureirorg(at)gmail(dot)com>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Csaba Nagy <ncslists(at)googlemail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Jaime Casanova <jaime(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: DELETE with LIMIT (or my first hack)
Date: 2010-12-01 14:37:22
Message-ID: AANLkTi=vqGX0xTo-XyBqskc-N=_39k51vXh5dDQKE6_G@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Dec 1, 2010 at 4:01 AM, Daniel Loureiro <loureirorg(at)gmail(dot)com> wrote:
> A) an feature MySQL-like which will DELETE/UPDATE just K tuples
> B) an feature to protect the database in case the DBA forget the "WHERE"
> statement
>

MySQL has B as well. To quote the manual:
"For beginners, a useful startup option is --safe-updates (or
--i-am-a-dummy, which has the same effect). This option was introduced
in MySQL 3.23.11. It is helpful for cases when you might have issued a
DELETE FROM tbl_name statement but forgotten the WHERE clause.
Normally, such a statement deletes all rows from the table. With
--safe-updates, you can delete rows only by specifying the key values
that identify them. This helps prevent accidents.
...
    *      You are not permitted to execute an UPDATE or DELETE
statement unless you specify a key constraint in the WHERE clause or
provide a LIMIT clause (or both). For example:

      UPDATE tbl_name SET not_key_column=val WHERE key_column=val;

      UPDATE tbl_name SET not_key_column=val LIMIT 1;

    *      The server limits all large SELECT results to 1,000 rows
unless the statement includes a LIMIT clause.
    *      The server aborts multiple-table SELECT statements that
probably need to examine more than 1,000,000 row combinations."

I have actually suggested that a certain subset of my users only
connect to the database if they are willing to use the --i-am-a-dummy
flag.


-- 
Rob Wultsch
wultsch(at)gmail(dot)com

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-12-01 14:41:08
Subject: Re: Proposal: First step towards Intelligent, integrateddatabase
Previous:From: Robert HaasDate: 2010-12-01 14:27:54
Subject: Re: GiST insert algorithm rewrite

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