Add Missing From?

Josh Berkus <josh@agliodbs.com> writes:

Going over the .conf, I just noticed that add_missing_from is still set to
True in postgresql.conf.sample. By my memory of our discussion, this
option was introduced in 7.4 and was to be set to False by default in 8.0.
Can we switch it to False?

I don't see anything in the current docs warning that such a change is
afoot. We have insisted on one release cycle's warning for smaller
things than this ...

Personally I don't mind (but bear in mind that the regression tests will
likely need fixes).

regards, tom lane

Tom,

I don't see anything in the current docs warning that such a change is
afoot. We have insisted on one release cycle's warning for smaller
things than this ...

Ok. Can we put a warning in, then? Where should we put it?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

I don't see anything in the current docs warning that such a change is
afoot. We have insisted on one release cycle's warning for smaller
things than this ...

Ok. Can we put a warning in, then? Where should we put it?

In the description of add_missing_from, I think.

regards, tom lane

On Sunday 08 August 2004 21:44, Josh Berkus wrote:

Tom,

I don't see anything in the current docs warning that such a change is
afoot. We have insisted on one release cycle's warning for smaller
things than this ...

Ok. Can we put a warning in, then? Where should we put it?

AFAIR this is still the only way to do updates on joined tables, a feature
that IIRC is in the sql spec (and certianly in other rdbms') that we do not
support. Until we come up with a solution for that, I think our current
behavior of defaulting on and emiting a warning upon occurance is the right
way to go.

--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:

AFAIR this is still the only way to do updates on joined tables, a feature
that IIRC is in the sql spec (and certianly in other rdbms') that we do not
support.

I think you're wrong on both counts --- we do support UPDATE FROM, and
it's not in the spec.

What we don't have is an equivalent syntax for DELETE, and you're right
that we probably ought to offer that before we deprecate add_missing_from.

regards, tom lane

Robert, Tom,

I think you're wrong on both counts --- we do support UPDATE FROM, and
it's not in the spec.

I can verify that it's not in SQL92. Unless you've got a place they added a
different syntax in 99 or 2003, Robert?

What we don't have is an equivalent syntax for DELETE, and you're right
that we probably ought to offer that before we deprecate add_missing_from.

Hmmm. What would that look like?

DELETE FROM table
{FROM | WITH | USING | ?? }
WHERE ...

I think we don't have this mainly because, what word do we use?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

Hmmm. What would that look like?

DELETE FROM table
{FROM | WITH | USING | ?? }
WHERE ...

I think we don't have this mainly because, what word do we use?

Yup, eggzackle. The implementation would really be trivial, but
previous discussion hung up on the problem of finding a syntax
people liked ...

regards, tom lane

Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

Hmmm. What would that look like?

DELETE FROM table
{FROM | WITH | USING | ?? }
WHERE ...

I think we don't have this mainly because, what word do we use?

Yup, eggzackle. The implementation would really be trivial, but
previous discussion hung up on the problem of finding a syntax
people liked ...

Agreed, but are we ever going to find any better solution than just
picking one of the suggestions?

I vote for the second FROM.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Sunday 08 August 2004 23:16, Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

Hmmm. What would that look like?

DELETE FROM table
{FROM | WITH | USING | ?? }
WHERE ...

I think we don't have this mainly because, what word do we use?

Yup, eggzackle. The implementation would really be trivial, but
previous discussion hung up on the problem of finding a syntax
people liked ...

Well, as yall have pointed out, the feature is not sql spec (for some reason I
thought it had been put in) so since the update syntax seems quite similar to
oracles, perhaps they can provide a pointer on delete syntax as well? I
can't seem to find my oracle syntax book, anyone have one handy ?

--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:

Well, as yall have pointed out, the feature is not sql spec (for some
reason I thought it had been put in) so since the update syntax seems
quite similar to oracles, perhaps they can provide a pointer on delete
syntax as well? I can't seem to find my oracle syntax book, anyone
have one handy ?

Didn't get any Oracle hits in a quick google, but I did find out that
MySQL spells it USING:

DELETE FROM target_tbl USING other_tbls WHERE ...

This isn't a particularly compelling precedent seeing that (a) MySQL
doesn't use our flavor of UPDATE syntax and (b) they only adopted the
above in 4.0.2. But it's better than no precedent. And frankly I
was having a big problem with "DELETE FROM target FROM others ..."
If that's not a recipe for confusion I don't know what is.

regards, tom lane

Didn't get any Oracle hits in a quick google, but I did find out that
MySQL spells it USING:

You guys can go to otn.oracle.com and register for free to get access to
all the documentation they've ever written. I've got an account there.
I do get the odd oracle magazine sent to me though...

This is the DELETE syntax:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_8005.htm#sthref6065

This is the text version:

DELETE [ hint ]
[ FROM ]
{ dml_table_expression_clause
| ONLY (dml_table_expression_clause)
}
[ t_alias ]
[ where_clause ]
[ returning_clause ] ;

Where dml_table_expression_clause is:

{ [ schema. ]
{ table
[ { PARTITION (partition)
| SUBPARTITION (subpartition)
}
| @ dblink
]
| { view | materialized view } [ @ dblink ]
}
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}

I can paste more but the way it works is that I can only view each
sub-part of the gammar separately so it's a pain in the butt to copy.

Chris

In article <5431.1092025741@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

Robert Treat <xzilla@users.sourceforge.net> writes:

Well, as yall have pointed out, the feature is not sql spec (for some
reason I thought it had been put in) so since the update syntax seems
quite similar to oracles, perhaps they can provide a pointer on delete
syntax as well? I can't seem to find my oracle syntax book, anyone
have one handy ?

Didn't get any Oracle hits in a quick google, but I did find out that
MySQL spells it USING:

DELETE FROM target_tbl USING other_tbls WHERE ...

This isn't a particularly compelling precedent seeing that (a) MySQL
doesn't use our flavor of UPDATE syntax and (b) they only adopted the
above in 4.0.2.

Actually, MySQL supports two different syntaxes for multi-table DELETEs:

1. DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
(introduced in MySQL 4.0.0)

2. DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
(introduced in MySQL 4.0.2)

On 8/9/2004 12:29 AM, Tom Lane wrote:

Robert Treat <xzilla@users.sourceforge.net> writes:

Well, as yall have pointed out, the feature is not sql spec (for some
reason I thought it had been put in) so since the update syntax seems
quite similar to oracles, perhaps they can provide a pointer on delete
syntax as well? I can't seem to find my oracle syntax book, anyone
have one handy ?

Didn't get any Oracle hits in a quick google, but I did find out that
MySQL spells it USING:

DELETE FROM target_tbl USING other_tbls WHERE ...

Feels much more understandable. The second FROM looks like a hickup.

Jan

This isn't a particularly compelling precedent seeing that (a) MySQL
doesn't use our flavor of UPDATE syntax and (b) they only adopted the
above in 4.0.2. But it's better than no precedent. And frankly I
was having a big problem with "DELETE FROM target FROM others ..."
If that's not a recipe for confusion I don't know what is.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Harald Fuchs <hf0722x@protecting.net> writes:

Actually, MySQL supports two different syntaxes for multi-table DELETEs:

1. DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
(introduced in MySQL 4.0.0)

2. DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
(introduced in MySQL 4.0.2)

Yeah. I ignored the first, as being so stupid that even the MySQL guys
soon realized what a bad idea it was ;-)

regards, tom lane

People,

DELETE FROM target_tbl USING other_tbls WHERE ...

Feels much more understandable. The second FROM looks like a hickup.

Yes, although imagine:

DELETE FROM staff USING users JOIN logons USING (user_id)
WHERE last_logon < ( now() - '6 months');

Not as bad as FROM, but still a bit baffling to look at. Still, I can't
think of anything else that wouldn't require inventing a new reserved word.

Oh, and MySQL's "multi-table deletes": PLEASE tell me that's not
SQL-standard.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

Oh, and MySQL's "multi-table deletes": PLEASE tell me that's not
SQL-standard.

It's not.

regards, tom lane

On 8/9/2004 12:53 PM, Josh Berkus wrote:

People,

DELETE FROM target_tbl USING other_tbls WHERE ...

Feels much more understandable. The second FROM looks like a hickup.

Yes, although imagine:

DELETE FROM staff USING users JOIN logons USING (user_id)
WHERE last_logon < ( now() - '6 months');

Not as bad as FROM, but still a bit baffling to look at. Still, I can't
think of anything else that wouldn't require inventing a new reserved word.

What about

DELETE FROM staff JOIN users ...

then?

Oh, and MySQL's "multi-table deletes": PLEASE tell me that's not
SQL-standard.

Yes, not standard.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:

What about
DELETE FROM staff JOIN users ...
then?

I don't much care for that, mainly because in my mind "x JOIN y" should
always be semantically equivalent to "y JOIN x". I think we want a real
clear syntactical separation between the deletion target table and the
other tables.

Also we do have the precedent of the way that UPDATE does things. We
don't want to use the keyword FROM because of confusion, but I think
we want to keep it basically the same as UPDATE.

regards, tom lane

In article <16580.1092071243@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

Jan Wieck <JanWieck@Yahoo.com> writes:

What about
DELETE FROM staff JOIN users ...
then?

I don't much care for that, mainly because in my mind "x JOIN y" should
always be semantically equivalent to "y JOIN x". I think we want a real
clear syntactical separation between the deletion target table and the
other tables.

Just one minor nit:

You're talking about "the deletion target table". Sorry to mention
the M word again, but MySQL allows deleting from more than one table
at the same time. Should we support that?