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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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.
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;
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
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
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
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
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
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
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
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,
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
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
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
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
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...
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
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
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
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
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
46 matches
Mail list logo
