Re: [HACKERS] [PATCHES] DELETE ... USING
Just to prevent a copy/paste error, I'd correct will no fail to will now fail in the suggested text below... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 10, 2005, at 5:49 PM, Josh Berkus wrote: Bruce, If everyone else is OK with having it fail, that is fine with me, but I wanted to make sure folks saw this was happening. I basically saw no discussion that we were disabling that syntax. [CC moved to hackers.] I believe we hashed this out when we added add_missing_from back in 7.3. In any case, yes, making that kind of query fail is intentional. So it should go in the release notes as a warning. Suggested text: add_missing_from now defaults to true. This means that queries such as the following: SELECT pg_class.*; DELETE FROM table_1 WHERE table_2.fk = table_1.key AND table_2.col3 = TRUE; ... will no fail with default settings. Either set add_missing_from to TRUE to re-enable them, or modify your application to support the correct syntax, such as the new DELETE FROM ... USING (see below). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] DELETE ... USING
Bruce, If everyone else is OK with having it fail, that is fine with me, but I wanted to make sure folks saw this was happening. I basically saw no discussion that we were disabling that syntax. [CC moved to hackers.] I believe we hashed this out when we added add_missing_from back in 7.3. In any case, yes, making that kind of query fail is intentional. So it should go in the release notes as a warning. Suggested text: add_missing_from now defaults to true. This means that queries such as the following: SELECT pg_class.*; DELETE FROM table_1 WHERE table_2.fk = table_1.key AND table_2.col3 = TRUE; ... will no fail with default settings. Either set add_missing_from to TRUE to re-enable them, or modify your application to support the correct syntax, such as the new DELETE FROM ... USING (see below). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] DELETE ... USING
Neil Conway wrote: Bruce Momjian wrote: I just checked current CVS and see exactly what you describe: test= SELECT pg_class.* LIMIT 0; ERROR: missing FROM-clause entry for table pg_class test= SET add_missing_from=true; SET test= SELECT pg_class.* LIMIT 0; NOTICE: adding missing FROM-clause entry for table pg_class Is this what we want? I don't think so. I thought we wanted to maintain the backward-compatible syntax of no FROM clause. We do? Why? It is just as noncompliant with the SQL spec as other variants of this behavior. add_missing_from would *always* have rejected those queries, so ISTM we have been discouraging this case for as long as add_missing_from has existed. If we want to allow this syntax by default, we will need to effectively redefine the meaning of add_missing_from -- which is fine, I just didn't think anyone wanted that. Oh, so by setting add_missing_from to false, this query starts to fail. I don't know how much people use that syntax. I use it sometimes as hack in psql to avoid typing FROM, but that's hardly a reason to support it. If everyone else is OK with having it fail, that is fine with me, but I wanted to make sure folks saw this was happening. I basically saw no discussion that we were disabling that syntax. [CC moved to hackers.] -- 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 ---(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
Re: [HACKERS] [PATCHES] DELETE ... USING
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: test= SELECT pg_class.* LIMIT 0; NOTICE: adding missing FROM-clause entry for table pg_class Is this what we want? I don't think so. I thought we wanted to maintain the backward-compatible syntax of no FROM clause. Well, the discussion earlier in the week concluded that add_missing_from=true should emit a notice in every case where add_missing_from=false would fail. Do you want to argue against that conclusion? I didn't realize that SELECT pg_class.* was now going to fail because add_missing_from is false. I didn't link those two together in my head, probably because the warning is not emitted if there is no FROM clause. Anyway, I am fine either way but wanted to publicise it at least. -- 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] DELETE ... USING
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Well, the discussion earlier in the week concluded that add_missing_from=true should emit a notice in every case where add_missing_from=false would fail. Do you want to argue against that conclusion? I didn't realize that SELECT pg_class.* was now going to fail because add_missing_from is false. It always has, though. Neil hasn't changed the behavior when add_missing_from is false ... he's only made add_missing_from=true emit a notice in *every* case where add_missing_from=false would fail. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] DELETE ... USING
[ 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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
