Re: Efficient DELETE Strategies

Christoph Haller <ch(at)rodos(dot)fzk(dot)de> writes:
> Based on an entry in the mailing list from 30 Oct 2001
> about efficient deletes on subqueries,
> I've found two ways to do so (PostgreSQL 7.2.1):
> ...
> Is there a way to put the second form (more complicated, but faster)
> in one statement?
> Or is there even a third way to delete, which I cannot see?

The clean way to do this would be to allow extra FROM-list relations
in DELETE. We already have a similar facility for UPDATE, so it's not
clear to me why there's not one for DELETE. Then you could do, say,

DELETE FROM onfvalue , onfvalue j WHERE
j.sid= 5 AND
onfvalue.lid = j.lid AND
onfvalue.mid = j.mid AND
onfvalue.timepoint = j.timepoint AND
onfvalue.entrancetime < j.entrancetime ;

If you were using two separate tables you could force this to happen
via an implicit FROM-clause entry, much as you've done in your second
alternative --- but there's no way to set up a self-join in a DELETE
because of the lack of any place to put an alias declaration.

AFAIK this extension would be utterly trivial to implement, since all
the machinery is there already --- for 99% of the backend, it doesn't
matter whether a FROM-item is implicit or explicit. We'd only need to
argue out what the syntax should be. I could imagine

DELETE FROM relation_expr [ , table_ref [ , ... ] ]
[ WHERE bool_expr ]

or

DELETE FROM relation_expr [ FROM table_ref [ , ... ] ]
[ WHERE bool_expr ]

The two FROMs in the second form look a little weird, but they help to
make a clear separation between the deletion target table and the
merely-referenced tables. Also, the first one might look to people
like they'd be allowed to write

DELETE FROM foo FULL JOIN bar ...

which is not any part of my intention (it's very unclear what it'd
mean for the target table to be on the nullable side of an outer join).
OTOH there'd be no harm in outer joins in a separate from-clause, eg

DELETE FROM foo FROM (bar FULL JOIN baz ON ...) WHERE ...

Actually, either syntax above would support that; I guess what's really
bothering me about the first syntax is that a comma suggests a list of
things that will all be treated similarly, while in reality the first
item will be treated much differently from the rest.

Does anyone know whether other systems that support the UPDATE extension
for multiple tables also support a DELETE extension for multiple tables?
If so, what's their syntax?

A somewhat-related issue is that people keep expecting to be able to
attach an alias to the target table name in UPDATE and DELETE; seems
like we get that question every couple months. While this is clearly
disallowed by the SQL spec, it's apparently supported by some other
implementations (else we'd not get the question so much). Should we
add that extension to our syntax? Or should we continue to resist it?

regards, tom lane