Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: [HACKERS] Efficient DELETE Strategies

Re: [HACKERS] Efficient DELETE Strategies

From
Bruce Momjian
Date:
Hannu Krosing wrote:
> What about
> 
> DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ]
>      [ WHERE bool_expr ]
> 
> or
> 
> DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ]
>      [ WHERE bool_expr ]

So make the initial FROM optional and allow the later FROM to be a list
of relations?  Seems kind of strange.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026

Re: [HACKERS] Efficient DELETE Strategies

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Hannu Krosing wrote:
>> What about
>> 
>> DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ]
>> [ WHERE bool_expr ]
>> 
>> or
>> 
>> DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ]
>> [ WHERE bool_expr ]

> So make the initial FROM optional and allow the later FROM to be a list
> of relations?  Seems kind of strange.

No, I think he's suggesting that one be able to pick out any element of
the FROM-list and say that that is the deletion target.  I really don't
want to get into that (unless there is precedent in Oracle or
someplace); it seems way too confusing to me.  It would also force us to
do error checking to eliminate cases that ought to just be syntactically
impossible: target table not present, target is a join or subselect
instead of a table, target is on wrong side of an outer join, etc.

[ and in another message ]
> The FROM ... FROM looks weird, and there is clearly confusion over the
> FROM t1, t2.  I wish there was another option.

The only other thing that's come to mind is to use a different keyword
(ie, not FROM) for the list of auxiliary relations.  WITH might work
from a simple readability point of view:DELETE FROM target WITH other-tables WHERE ...
But we've already got FROM as the equivalent construct in UPDATE, so it
seems weird to use something else in DELETE.
        regards, tom lane

Re: [HACKERS] Efficient DELETE Strategies

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Hannu Krosing wrote:
> >> What about
> >> 
> >> DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ]
> >> [ WHERE bool_expr ]
> >> 
> >> or
> >> 
> >> DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ]
> >> [ WHERE bool_expr ]
> 
> > So make the initial FROM optional and allow the later FROM to be a list
> > of relations?  Seems kind of strange.
> 
> No, I think he's suggesting that one be able to pick out any element of
> the FROM-list and say that that is the deletion target.  I really don't
> want to get into that (unless there is precedent in Oracle or
> someplace); it seems way too confusing to me.  It would also force us to
> do error checking to eliminate cases that ought to just be syntactically
> impossible: target table not present, target is a join or subselect
> instead of a table, target is on wrong side of an outer join, etc.

Yuck.

> [ and in another message ]
> > The FROM ... FROM looks weird, and there is clearly confusion over the
> > FROM t1, t2.  I wish there was another option.
> 
> The only other thing that's come to mind is to use a different keyword
> (ie, not FROM) for the list of auxiliary relations.  WITH might work
> from a simple readability point of view:
>     DELETE FROM target WITH other-tables WHERE ...
> But we've already got FROM as the equivalent construct in UPDATE, so it
> seems weird to use something else in DELETE.

Yes, another keyword is the only solution.  Having FROM after DELETE
mean something different from FROM after a tablename is just too weird. 
I know UPDATE uses FROM, and it is logical to use it here, but it is
just too wierd when DELETE already has a FROM.  Should we allow FROM and
add WITH to UPDATE as well, and document WITH but support FROM too?  No
idea.  What if we support ADD FROM as the keywords for the new clause?

Clearly this is a TODO item.  I will document it when we decide on a
direction.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026

Re: [HACKERS] Efficient DELETE Strategies

From
Hannu Krosing
Date:
On Tue, 2002-06-11 at 04:53, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Hannu Krosing wrote:
> > >> What about
> > >> 
> > >> DELETE relation_expr FROM relation_expr [ , table_ref [ , ... ] ]
> > >> [ WHERE bool_expr ]
> > >> 
> > >> or
> > >> 
> > >> DELETE relation_expr.* FROM relation_expr [ , table_ref [ , ... ] ]
> > >> [ WHERE bool_expr ]
> > 
> > > So make the initial FROM optional and allow the later FROM to be a list
> > > of relations?  Seems kind of strange.

I was inspired by MS Access syntax that has optional relation_expr.* :
  DELETE [relation_expr.*] FROM relation_expr WHERE criteria

it does not allow any other tablerefs in from 

> Clearly this is a TODO item.  I will document it when we decide on a
> direction.

Or then we can just stick with standard syntax and teach people to do

DELETE FROM t1 where t1.id1 in (select id2 from t2 where t2.id2 = t1.id1)

and perhaps even teach our optimizer to add the t2.id2 = t1.id1 part
itself to make it fast

AFAIK this should be exactly the same as the proposed

DELETE FROM t1 FROM t2
WHERE t2.id2 = t1.id1

--------------
Hannu