Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-02 Thread Bruce Momjian
Peter Eisentraut wrote: On tis, 2010-11-30 at 14:20 -0500, Andrew Dunstan wrote: 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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-02 Thread Robert Haas
On Thu, Dec 2, 2010 at 7:12 PM, Bruce Momjian br...@momjian.us wrote: Peter Eisentraut wrote: On tis, 2010-11-30 at 14:20 -0500, Andrew Dunstan wrote: I agree, that argument is completely misconceived. If the DBA is paying enough attention to use LIMIT, s/he should be paying enough

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-02 Thread Bruce Momjian
Robert Haas wrote: On Thu, Dec 2, 2010 at 7:12 PM, Bruce Momjian br...@momjian.us wrote: Peter Eisentraut wrote: On tis, 2010-11-30 at 14:20 -0500, Andrew Dunstan wrote: I agree, that argument is completely misconceived. If the DBA is paying enough attention to use LIMIT, s/he should be

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Daniel Loureiro
its pretty clear to me that's 2 different needs here, both linked to DELETE/UPDATE behavior. 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 I think that the first feature its pretty reasonable for

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Valentine Gogichashvili
Hi, actually introducing LIMIT and OFFSET with ORDER BY to DELETE/UPDATE would make it much easier to push data from one (say queue) table to another. And to fetch chunks of queue entries updating their status in one statement. Now I have to do SELECT...ORDER BY...LIMIT and then do some magic

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Marko Tiikkaja
On 2010-12-01 1:46 PM, Valentine Gogichashvili wrote: And speaking about pushing data from one table to another, what I really would like to be able to do would be also something like: INSERT INTO ... DELETE FROM ... WHERE... ORDER BY.. [LIMIT...] RETURNING...; this would be also quite

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Rob Wultsch
On Wed, Dec 1, 2010 at 4:01 AM, Daniel Loureiro loureir...@gmail.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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Mario Weilguni
Am 01.12.2010 15:37, schrieb Rob Wultsch: 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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Kevin Grittner
Mario Weilguni roadrunn...@gmx.at wrote: Is it really up to the database to decide what queries are ok? It's the task of the developers to test their applikations. We're talking about ad hoc queries here, entered directly through psql or similar. -Kevin -- Sent via pgsql-hackers mailing

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Josh Berkus
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. Queueing. If logless tables are in 9.1, then using PostgreSQL as the backend for a queue becomes a sensible thing to do. And what

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Dmitriy Igrishin
Hey, I don't clearly understand why anybody should perform DELETE directly from a psql terminal on a production system. WHY ? I can't understand what problem with DELETE without WHERE clause for application developers and why DBMS should protect them from DELETE FROM table. PS. Anybody can

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-12-01 Thread Peter Eisentraut
On tis, 2010-11-30 at 14:20 -0500, Andrew Dunstan wrote: 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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Csaba Nagy
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Rob Wultsch
On Mon, Nov 29, 2010 at 10:50 PM, Marti Raudsepp ma...@juffo.org wrote: On Tue, Nov 30, 2010 at 05:09, Jaime Casanova ja...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Robert Haas
On Tue, Nov 30, 2010 at 4:25 AM, Csaba Nagy ncsli...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan and...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Andrew Dunstan
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Robert Haas
On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan and...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane t...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Csaba Nagy
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Robert Haas
On Tue, Nov 30, 2010 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Unfortunately, the UPDATE case would be an order of magnitude harder (think inheritance trees where the children

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Daniel Loureiro
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Daniel Loureiro
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Kevin Grittner
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. If you wanted protection from that you'd want

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Andrew Dunstan
On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureirodan...@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.

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Marko Tiikkaja
On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureirodan...@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.

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Jeff Davis
On Tue, 2010-11-30 at 11:12 -0500, Robert Haas wrote: 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.

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Marko Tiikkaja
While reading this thread, I thought of two things I think we could do if this feature was implemented: 1. Sort large UPDATE/DELETEs so it is done in heap order This is actually a TODO item. I imagine it would be possible to do something like: DELETE FROM foo USING (...) ORDER BY ctid;

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Andres Freund
On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote: On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureirodan...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Alastair Turner
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 Loureirodan...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Andrew Dunstan
On 11/30/2010 03:16 PM, Andres Freund wrote: On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote: On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureirodan...@termasa.com.brwrote: to me the key its security - its a anti-DBA-with-lack-of-attention feature. Well, it seems

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Robert Haas
On Tue, Nov 30, 2010 at 2:45 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2010-11-30 at 11:12 -0500, Robert Haas wrote: 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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Tom Lane
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: While reading this thread, I thought of two things I think we could do if this feature was implemented: 1. Sort large UPDATE/DELETEs so it is done in heap order This is actually a TODO item. I imagine it would be possible to do

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Dimitri Fontaine
Andres Freund and...@anarazel.de writes: On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote: 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? Because the delete of the

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: You seem to be imagining the MergeAppend node on top, but I had it in the other order in my mind. The ModifyTable node would be the outermost plan node, pulling from the Limit, which would deliver the first n table rows from the MergeAppend, which

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Alvaro Herrera
Excerpts from Daniel Loureiro's message of mar nov 30 15:04:17 -0300 2010: So I guess that I have choose the wrong hack to start. So it seems :-D -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development,

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Jeff Davis
On Tue, 2010-11-30 at 15:52 -0500, Robert Haas wrote: On Tue, Nov 30, 2010 at 2:45 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2010-11-30 at 11:12 -0500, Robert Haas wrote: Could you possibly have ModifyTable - Limit - MergeAppend? Before MergeAppend knows which tuple to produce, it

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-30 Thread Bruce Momjian
Daniel Loureiro wrote: 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

[HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Daniel Loureiro
Hi, 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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Jaime Casanova
On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro loureir...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Daniel Loureiro
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Jaime Casanova
On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro loureir...@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...

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Robert Haas
On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro loureir...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Robert Haas
On Mon, Nov 29, 2010 at 10:09 PM, Jaime Casanova ja...@2ndquadrant.com wrote: On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro loureir...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Andrew Dunstan
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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Robert Haas
On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan and...@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

Re: [HACKERS] DELETE with LIMIT (or my first hack)

2010-11-29 Thread Marti Raudsepp
On Tue, Nov 30, 2010 at 05:09, Jaime Casanova ja...@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