Re: DELETE ... USING

Euler Taveira de Oliveira wrote:
> I'm worried about add_missing_from enabled.

The plan is to make add_missing_from default to false in 8.1

> euler=# delete from t3 using t1 where b > 500;
> DELETE 4
> euler=# select * from t3;
>  x | y
> ---+---
> (0 rows)
>
> In this case, I 'forget' to do the join and it delete all rows from t3.
> I know that user needs to pay attention, but ... What about default
> add_missing_from to off?

add_missing_from would not make any difference here. The problem is that
there is no join clause between t3 and t1, not that t1 is being
implicitly added to the range table (which is what add_missing_from
would warn you about).

The problem is analogous to a SELECT like:

SELECT * FROM t3, t1 WHERE b > 500;

i.e. forgetting to specify a join clause and therefore accidentally
computing the cartesian product. There has been some gripping recently
on -hackers about disabling this or emitting a warning of some kind.

> euler=# select * from t1 where t1.a = t3.x;
> NOTICE:  adding missing FROM-clause entry for table "t3"
> NOTICE:  adding missing FROM-clause entry for table "t3"
>  a | b
> ---+----
>  5 | 10
> (1 row)
>
> euler=# delete from t1 where t1.a = t3.x;
> DELETE 1
> euler=#
>
> I think we need at least a NOTICE here. Of course it could be extended
> to UPDATE too.

I can see an argument for having a NOTICE here. On the other hand,
add_missing_from will default to false in 8.1, so presumably the only
people enabling it will be those who specifically need backward
compatibility for old applications that they cannot afford to change.
Filling the logs with bogus NOTICEs would be sufficiently annoying it
would probably force some people to modify their applications, thereby
defeating the point of having a backward compatibility GUC variable in
the first place.

> BTW, what about regression tests for UPDATE ... FROM?

I agree regression tests would be useful -- you are welcome to send a
patch :)

-Neil