Robert Haas wrote:
> On Thu, Dec 2, 2010 at 7:12 PM, Bruce Momjian 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 pa
On Thu, Dec 2, 2010 at 7:12 PM, Bruce Momjian 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
>> > attention no
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 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 comp
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 perf
> 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 wha
Mario Weilguni 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 list (pgsql-hacke
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 WH
On Wed, Dec 1, 2010 at 4:01 AM, Daniel Loureiro 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 --
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 efficien
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 wi
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
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 ca
On Tue, 2010-11-30 at 15:52 -0500, Robert Haas wrote:
> On Tue, Nov 30, 2010 at 2:45 PM, Jeff Davis 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 ne
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
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via
Robert Haas 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 would be reponsible f
Andres Freund 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 whole table may
Marko Tiikkaja 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
> something like:
> DELETE FROM
On Tue, Nov 30, 2010 at 2:45 PM, Jeff Davis 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 one Sort
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 Loureirowrote:
to me the key its security - its a anti-DBA-with-lack-of-attention
feature.
Well, it seems pretty weak to me for th
On Tue, Nov 30, 2010 at 9:24 PM, Marko Tiikkaja
wrote:
>> On 11/30/2010 02:12 PM, Kevin Grittner wrote:
>>>
>>> Daniel Loureiro 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 t
On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote:
> > On 11/30/2010 02:12 PM, Kevin Grittner wrote:
> >> Daniel Loureiro 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
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;
wi
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 rowtyp
On 11/30/2010 02:12 PM, Kevin Grittner wrote:
Daniel Loureiro 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.
I agree, that argume
On 11/30/2010 02:12 PM, Kevin Grittner wrote:
Daniel Loureiro 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.
I agree, that argum
Daniel Loureiro 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 more of an "assert l
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
> 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 c
On Tue, Nov 30, 2010 at 11:04 AM, Tom Lane wrote:
> Robert Haas writes:
>> On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane 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
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 f
Robert Haas writes:
> On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane 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
On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane wrote:
> Robert Haas writes:
>> On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan 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 pe
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
Robert Haas writes:
> On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan 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 ma
On Tue, Nov 30, 2010 at 4:25 AM, Csaba Nagy 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
On Mon, Nov 29, 2010 at 10:50 PM, Marti Raudsepp wrote:
> On Tue, Nov 30, 2010 at 05:09, Jaime Casanova 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 th
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
On Tue, Nov 30, 2010 at 05:09, Jaime Casanova 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 wi
On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan 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
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 10:09 PM, Jaime Casanova wrote:
> On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro 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 claus
On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro 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 wo
On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro 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
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 f
On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro 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 someth
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
47 matches
Mail list logo
