This patch is a cleaned up version of Euler Taveira de Oliveira's patch
implementing DELETE ... USING. I removed a bunch of unused code (no need
to tlist transformations), updated copyfuncs/equalfuncs, improved the
documentation, rearranged a few things, and added regression tests. I
haven't done psql tab completion. Barring any objections, I'll apply
this to HEAD tomorrow.

On a related note, UPDATE uses the FROM keyword to denote the list of
relations to join with, whereas DELETE uses USING. Should we make USING
an alias for FROM in UPDATE and if so, should we deprecate FROM? This
would be more consistent, which I suppose is a good thing.

-Neil


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Neil Conway <neilc@samurai.com> writes:
> On a related note, UPDATE uses the FROM keyword to denote the list of
> relations to join with, whereas DELETE uses USING. Should we make USING
> an alias for FROM in UPDATE and if so, should we deprecate FROM? This
> would be more consistent, which I suppose is a good thing.

Of course, the entire reason this didn't happen years ago is that we
couldn't agree on what keyword to use... you sure you want to reopen
that discussion?

I don't think changing UPDATE is a good idea. It's consistent with
SELECT and people are used to it.

You could argue that something like

DELETE FROM target [ { USING | FROM } othertables ] ...

is the best compromise. Those who like consistency can write FROM,
those who don't like "FROM a FROM b" can write something else.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

BTW, this patch is lacking ruleutils.c support. Put a DELETE USING
into a rule and see whether pg_dump will dump the rule correctly ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
> BTW, this patch is lacking ruleutils.c support. Put a DELETE USING
> into a rule and see whether pg_dump will dump the rule correctly ...

Good catch; a revised patch is attached.

-Neil


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

[ CC'ing hackers to see if anyone else wants to weigh in ]

Tom Lane wrote:
> Of course, the entire reason this didn't happen years ago is that we
> couldn't agree on what keyword to use... you sure you want to reopen
> that discussion?

Sure, it doesn't seem too difficult to settle to me.

> I don't think changing UPDATE is a good idea. It's consistent with
> SELECT and people are used to it.

Fair enough, I can't get too excited about it either.

> You could argue that something like
>
> DELETE FROM target [ { USING | FROM } othertables ] ...
>
> is the best compromise. Those who like consistency can write FROM,
> those who don't like "FROM a FROM b" can write something else.

This would be fine with me. Are there any other opinions out there on
what syntax would be best for this feature? (For those on -hackers, the
feature in question is adding the ability to specify additional tables
to "join" against in a DELETE, as can be done using FROM in UPDATE.)

-Neil

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Hi Neil,


> > BTW, this patch is lacking ruleutils.c support. Put a DELETE USING
> > into a rule and see whether pg_dump will dump the rule correctly
> ...
>
> Good catch; a revised patch is attached.
>
Greate job. But I'm worried about add_missing_from enabled. See:

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?

The other case is:

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.

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

PS> all examples are extracted from regression database.



Euler Taveira de Oliveira
euler[at]yahoo_com_br





Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Neil Conway <neilc@samurai.com> writes:
> Euler Taveira de Oliveira wrote:
>> 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, ...

.... but when it is TRUE, there should be a notice, same as there is in
SELECT. UPDATE should produce such a notice too, IMHO. Probably we
omitted the message originally because there was no way to avoid it
in a DELETE, but now there will be.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane wrote:
> ... but when it is TRUE, there should be a notice, same as there is in
> SELECT. UPDATE should produce such a notice too, IMHO. Probably we
> omitted the message originally because there was no way to avoid it
> in a DELETE, but now there will be.

Well, my previous message described why I'm not sure that this line of
reasoning is correct. I think the only really proper configuration is
add_missing_from=false and an explicit USING/FROM list. Just about the
only reason to enable add_missing_from would be for compatibility with
previous releases of PostgreSQL -- and that "compatible" behavior is not
to issue a warning for UPDATE and DELETE in this situation. If the user
deliberately enables add_missing_from, I'm inclined to trust them that
they know what they're doing.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Neil Conway <neilc@samurai.com> writes:
> Well, my previous message described why I'm not sure that this line of
> reasoning is correct. I think the only really proper configuration is
> add_missing_from=false and an explicit USING/FROM list. Just about the
> only reason to enable add_missing_from would be for compatibility with
> previous releases of PostgreSQL -- and that "compatible" behavior is not
> to issue a warning for UPDATE and DELETE in this situation.

Hmm. There's some merit in that position, but consider this: we are
encouraging people rather strongly to move to the add_missing_from=false
behavior. So add_missing_from=true could be seen as a testing situation
in which you'd like to know which of your queries have a problem, while
not actually causing your app to fail. Strict backwards compatibility
won't produce the warning but also won't help you find what will break.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster