DELETE with LIMIT (or my first hack)

On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro <loureirorg@gmail.com> wrote:

3) change the executor to stop after “n” successful iterations. Is
this correct ?

no. it means you will delete the n first tuples that happen to be
found, if you don't have a WHERE clause that means is very possible
you delete something you don't want to... the correct solution is to
use always try DELETE's inside transactions and only if you see the
right thing happening issue a COMMIT

besides i think this has been proposed and rejected before

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM
tuples - its wrong to get RANDOM tuples ? So, in the same logic, its wrong
to exclude n random tuples ? Besides, if you want DELETE just 1 tuple, why
the executor have to scan the entire table, and not just stoping after find
the 1 tuple ? Why the LIMIT clause should be used to speedup only SELECT
statements ? if the programmer know the expected number of affected rows why
not use it to speed up DELETE/UPDATE ?

cheers,
--
Daniel Loureiro
http://diffcoder.blogspot.com/

2010/11/30 Jaime Casanova <jaime@2ndquadrant.com>

On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro <loureirorg@gmail.com> wrote:

good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM
tuples

no. at least IMHO the only sensible way that LIMIT is usefull is with
an ORDER BY clause with make the results very well defined...

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro <loureirorg@gmail.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

On Mon, Nov 29, 2010 at 10:09 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote:

On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro <loureirorg@gmail.com> wrote:

good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM
tuples

no. at least IMHO the only sensible way that LIMIT is usefull is with
an ORDER BY clause with make the results very well defined...

That's not 100% true - it can sometimes be very useful when digging
through a database to grab 50 rows from a table just to get a feel for
what kind of stuff in there. Maybe it's stupid, but I find it handy.
But even granting the premise, that's an argument for making DELETE
support both ORDER BY and LIMIT, not for supporting neither of them.
For example, suppose we're trying to govern an ancient Greek
democracy:

http://en.wikipedia.org/wiki/Ostracism

DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1;

I think the executor already pretty much knows how to do this. The
planner might need some fiddling to hand over the correct
instructions, not sure. But this might not even be super hard, though
Daniel might want to pick something a little less ambitious for his
very first project, because debugging planner and executor problems is
not so easy.

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

On 11/29/2010 10:19 PM, Robert Haas wrote:

For example, suppose we're trying to govern an ancient Greek
democracy:

http://en.wikipedia.org/wiki/Ostracism

DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1;

I'm not sure this is a very good example. Assuming there isn't a tie,
I'd do it like this:

DELETE FROM residents_of_athens
WHERE ostracism_votes >= 6000
and ostracism_votes =
(SELECT max(ostracism_votes)
FROM residents_of_athens);

I can't say I'd be excited by this feature. In quite a few years of writing SQL I don't recall ever wanting such a gadget.

cheers

andrew

On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 11/29/2010 10:19 PM, Robert Haas wrote:

For example, suppose we're trying to govern an ancient Greek
democracy:

http://en.wikipedia.org/wiki/Ostracism

DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1;

I'm not sure this is a very good example. Assuming there isn't a tie, I'd do
it like this:

DELETE FROM residents_of_athens
WHERE ostracism_votes >= 6000
   and ostracism_votes =
    (SELECT max(ostracism_votes)
     FROM residents_of_athens);

That might be a lot less efficient, though, and sometimes it's not OK
to delete more than one record. Imagine, for example, wanting to
dequeue the work item with the highest priority. Sure, you can use
SELECT ... LIMIT to identify one and then DELETE it by some other key,
but DELETE .. ORDER BY .. RETURNING .. LIMIT would be cool, and would
let you do it with just one scan.

I can't say I'd be excited by this feature. In quite a few years of writing
SQL I don't recall ever wanting such a gadget.

It's something I've wanted periodically, though not badly enough to do
the work to make it happen.

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

On Tue, Nov 30, 2010 at 05:09, Jaime Casanova <jaime@2ndquadrant.com> wrote:

at least IMHO the only sensible way that LIMIT is usefull is with
an ORDER BY clause with make the results very well defined...

DELETE with LIMIT is also useful for deleting things in batches, so
you can do large deletes on a live system without starving other users
from I/O. In this case deletion order doesn't matter (it's more
efficient to delete rows in physical table order) -- ORDER BY isn't
necessary.

Regards,
Marti

Hi all,

The workaround recommended some time ago by Tom is:

DELETE FROM residents_of_athens WHERE ctid = any(array(SELECT ctid FROM
residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1));

It is about as efficient as the requested feature would be, just uglier
to write down. I use it all the time when batch-deleting something large
(to avoid long running transactions and to not crash slony). It also
helps to vacuum frequently if you do that on large amount of data...

Cheers,
Csaba.

On Mon, Nov 29, 2010 at 10:50 PM, Marti Raudsepp <marti@juffo.org> wrote:

On Tue, Nov 30, 2010 at 05:09, Jaime Casanova <jaime@2ndquadrant.com> wrote:

at least IMHO the only sensible way that LIMIT is usefull is with
an ORDER BY clause with make the results very well defined...

DELETE with LIMIT is also useful for deleting things in batches, so
you can do large deletes on a live system without starving other users
from I/O. In this case deletion order doesn't matter (it's more
efficient to delete rows in physical table order) -- ORDER BY isn't
necessary.

Regards,
Marti

++

I have a lot of DELETE with LIMIT in my (mysql) environment for this reason.

--
Rob Wultsch
wultsch@gmail.com

On Tue, Nov 30, 2010 at 4:25 AM, Csaba Nagy <ncslists@googlemail.com> wrote:

The workaround recommended some time ago by Tom is:

DELETE FROM residents_of_athens WHERE ctid = any(array(SELECT ctid FROM
residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1));

It is about as efficient as the requested feature would be, just uglier
to write down. I use it all the time when batch-deleting something large
(to avoid long running transactions and to not crash slony). It also
helps to vacuum frequently if you do that on large amount of data...

That's a very elegant hack, but not exactly obvious to a novice user
or, say, me. So I think it'd be nicer to have the obvious syntax
work.

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

Hi Robert,

On Tue, 2010-11-30 at 09:19 -0500, Robert Haas wrote:

That's a very elegant hack, but not exactly obvious to a novice user
or, say, me. So I think it'd be nicer to have the obvious syntax
work.

I fully agree - but you first have to convince core hackers that this is
not just a foot-gun. This was discussed many times in the past, patches
were also offered (perhaps not complete one, but proving that there is
an itch getting scratched):

http://archives.postgresql.org/pgsql-patches/2002-09/msg00255.php

The reaction:

http://archives.postgresql.org/pgsql-patches/2002-09/msg00256.php

There are other discussions too, if I remember correctly Tom once
admitted that the core of implementing the feature would likely consist
in letting it work, as the infrastructure is there to do it but it is
actively disabled. I can't find the mail now though.

So it is really an ideological thing and not lack of demand or
implementation attempts... I for myself can't write working C code
anyway, so I got my peace with the workaround - I wish you good luck
arguing Tom :-)

Cheers,
Csaba.

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

I can't say I'd be excited by this feature. In quite a few years of writing
SQL I don't recall ever wanting such a gadget.

It's something I've wanted periodically, though not badly enough to do
the work to make it happen.

It would certainly look like nothing but a crude hack if the feature is
only available for DELETE and not UPDATE. Unfortunately, the UPDATE
case would be an order of magnitude harder (think inheritance trees
where the children aren't all alike).

regards, tom lane

On 11/30/2010 09:57 AM, Csaba Nagy wrote:

So it is really an ideological thing and not lack of demand or
implementation attempts... I for myself can't write working C code
anyway, so I got my peace with the workaround - I wish you good luck
arguing Tom :-)

We need a convincing use case for it. So far the only one that's seemed
at all convincing to me is the one about deleting in batches. But that
might be enough.

As for it being illogical, I don't think it's any more so than

DELETE FROM foo WHERE random() < 0.1;

and you can do that today.

cheers

andrew

On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

I can't say I'd be excited by this feature. In quite a few years of writing
SQL I don't recall ever wanting such a gadget.

It's something I've wanted periodically, though not badly enough to do
the work to make it happen.

It would certainly look like nothing but a crude hack if the feature is
only available for DELETE and not UPDATE.

I'm not sure this is true, given Andrew's comment that the bulk
deletion argument is the only one he finds compelling, but I'd surely
be in favor of supporting both.

Unfortunately, the UPDATE
case would be an order of magnitude harder (think inheritance trees
where the children aren't all alike).

I don't understand why there's anything more to this than sticking a
Limit node either immediately above or immediately below the
ModifyTable node.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Unfortunately, the UPDATE
case would be an order of magnitude harder (think inheritance trees
where the children aren't all alike).

I don't understand why there's anything more to this than sticking a
Limit node either immediately above or immediately below the
ModifyTable node.

1. You need to support ORDER BY too, otherwise I *will* be on the
warpath against this as a foot-gun with no redeeming social value.

2. So what you need is Sort underneath Limit underneath ModifyTable.
Putting them above it would be quite the wrong semantics.

3. This doesn't work tremendously well for inheritance trees, where
ModifyTable acts as sort of an implicit Append node. You can't just
funnel all the tuples through one Sort or Limit node because they aren't
all the same rowtype. (Limit might perhaps not care, but Sort will.)
But you can't have a separate Sort/Limit for each table either, because
that would give the wrong behavior. Another problem with funneling all
the rows through one Sort/Limit is that ModifyTable did need to know
which table each row came from, so it can apply the modify to the right
table.

I don't offhand see a solution other than integrating the responsibility
for limit-counting and sorting into ModifyTable itself, making it into
an unholy union of ModifyTable+Limit+MergeAppend (with the individual
inputs required to deliver sorted outputs separately). That's
sufficiently ugly, and probably bad for performance in the normal case,
that I don't think it's going to be acceptable for such a marginal
feature.

Or I guess you could try to persuade us that DELETE/UPDATE with ORDER BY
or LIMIT doesn't need to support inherited target tables. I wouldn't
bet on that proposal flying either.

regards, tom lane

On Tue, Nov 30, 2010 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Unfortunately, the UPDATE
case would be an order of magnitude harder (think inheritance trees
where the children aren't all alike).

I don't understand why there's anything more to this than sticking a
Limit node either immediately above or immediately below the
ModifyTable node.

1. You need to support ORDER BY too, otherwise I *will* be on the
warpath against this as a foot-gun with no redeeming social value.

Will you be wielding a Tom-ahawk?

2. So what you need is Sort underneath Limit underneath ModifyTable.
Putting them above it would be quite the wrong semantics.

OK.

3. This doesn't work tremendously well for inheritance trees, where
ModifyTable acts as sort of an implicit Append node.  You can't just
funnel all the tuples through one Sort or Limit node because they aren't
all the same rowtype.  (Limit might perhaps not care, but Sort will.)
But you can't have a separate Sort/Limit for each table either, because
that would give the wrong behavior.  Another problem with funneling all
the rows through one Sort/Limit is that ModifyTable did need to know
which table each row came from, so it can apply the modify to the right
table.

Could you possibly have ModifyTable -> Limit -> MergeAppend?

Or I guess you could try to persuade us that DELETE/UPDATE with ORDER BY
or LIMIT doesn't need to support inherited target tables.  I wouldn't
bet on that proposal flying either.

I've spent enough time worrying about the fact that tables with
inheritance children don't behave as nicely as those that don't to
have any interest in going in the other direction.

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

to me the key its security - its a anti-DBA-with-lack-of-attention feature.
If i forget the "WHERE" statement, I will delete some valid tuples and
messed up the bd, but its less-than-worst that exclude all the table. A DBA
who never forgot an "WHERE" in an "DELETE" is not an DBA. Just kidding, but
this happens often enough.

is there another option to implement this ? Its possible to be done by
plugins/extension (in a Firefox browser style) ?

Sds,
--
Daniel Loureiro
------------------------------

2010/11/30 Andrew Dunstan <andrew@dunslane.net>

3. This doesn't work tremendously well for inheritance trees, where
ModifyTable acts as sort of an implicit Append node. You can't just
funnel all the tuples through one Sort or Limit node because they aren't
all the same rowtype. (Limit might perhaps not care, but Sort will.)
But you can't have a separate Sort/Limit for each table either, because
that would give the wrong behavior. Another problem with funneling all
the rows through one Sort/Limit is that ModifyTable did need to know
which table each row came from, so it can apply the modify to the right
table.

So I guess that I have choose the wrong hack to start.

Just for curiosity, why the result of "WHERE" filter (in
SELECT/DELETE/UPDATE) is not put in memory, i.e. an array of ctid, like an
buffer and then executed by SELECT/DELETE/UPDATE at once ?

Greets,
--
Daniel Loureiro