Re: Add Missing From? - Mailing list pgsql-hackers

On Wed, 11 Aug 2004, Harald Fuchs wrote:

> In article <20040811074837.Q17280@megazone.bigpanda.com>,
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>
> >> In exactly the same order as for single-table DELETEs -
> >> implementation-defined.
>
> > I think you probably meant in an unspecified order.
> > Implementation-defined really doesn't mean anything when you're trying to
> > define what it means for a particular implementation. ;)
>
> You're right - what I meant was something like "the same order in
> which SELECT without an ORDER BY returns its rows" - something you
> can't rely upon.

Right, the reason it's important is that there are some things now that
are potentially tied together. If you have table A with rows A1,...,An and
table B with rows B1,...,Bm and the delete join condition gives the two
outputs (A1,B1) and (A2,B1) does a before trigger returning NULL for B1
prevent A1 and A2 from being deleted?  Do row triggers for B1 get run
twice? If the row trigger for B1 is run twice and say returns NULL on the
first but not the second, do A1 and B1 get deleted but not A2?  I'm also
afraid that running the triggers multiple times could actually break
people's current triggers.  Also, if we do allow multiple table at a time
deletes, should we be considering multiple table at a time updates, and if
so, what works for those.

I'm also not sure how rules apply to these multi-table delete statements.

> >> > B) If no join criteria are supplied, is it OK to delete all rows from one of
> >> > the tables?
> >>
> >> Yes - people creating Cartesian products deserve punishment :-)
>
> > What we do here should be related to what we would want to happen on
> > a view with a join if we were to make automatically updatable views.
>
> I think automatically updatable views are something like views with
> automatically generated INSERT/UPDATE/DELETE rules, aren't they?
>
> Well, my hypothetical multi-table-DELETE would then call the DELETE
> rule.  Where do you see a problem?

It's not a problem, but I'd think that:

delete from foo, bar where foo.val=3;
should probably act similarly to:
create view v as select * from foo,bar where foo.val=3;
delete from v;
or probably even:
create view v as select * from foo,bar;
delete from v where val=3;

If that's true, we're not merely defining behavior for the first, but
defining what we expect to make the behavior for the latter two as well so
we should think about that as well.

> >> I thought about something like that:
> >>
> >> DELETE [tbl [,tbl]...]
> >> FROM fromexp
> >>
> >> "fromexp" could be anything which is legal after a "SELECT ... FROM",
> >> including outer joins and LIMIT clauses.
>
> > Including subselects in FROM? That's a big barrel of fish.  How does this
> > interact with subselects with union or distinct or group by.
>
> Ouch, didn't think about that.
>
> The general problem seems to be that a table can occur at many places
> within one query, and at each place different rows are matched.  On
> the top level this should not be a problem: just specify the correct
> table alias between DELETE and FROM.

That's not quite the issue I was thinking of. I was thinking of cases
where the output rows are not directly/easily connected to base table
rows, likedelete foo from (select sum(a) from tab group by b having sum(a)>10) foo;

> Perhaps we could disallow deleting from tables/aliases in deeper
> subselect levels?

That's probably a good thing for any first implementation. It'd probably
still be good to think about those cases to not box out possible future
enhancements.