Skip site navigation (1) Skip section navigation (2)

Re: DELETE ... USING

From: Neil Conway <neilc(at)samurai(dot)com>
To: Euler Taveira de Oliveira <eulerto(at)yahoo(dot)com(dot)br>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: DELETE ... USING
Date: 2005-04-05 02:49:19
Message-ID: 4251FCAF.4070909@samurai.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-04-05 03:30:58
Subject: Re: DELETE ... USING
Previous:From: Greg Sabino MullaneDate: 2005-04-05 02:46:18
Subject: Re: [GENERAL] plPHP in core?

pgsql-patches by date

Next:From: Tom LaneDate: 2005-04-05 03:30:58
Subject: Re: DELETE ... USING
Previous:From: Euler Taveira de OliveiraDate: 2005-04-05 02:15:04
Subject: Re: DELETE ... USING

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group