Re: [HACKERS] Efficient DELETE Strategies

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026