Dear Friends,

First, thank you very much for considering a fix on the GROUP BY issue.
I am starting a new thread about another issue:

It seems that DELETE cannot understand INNER JOINS and needs HAVING.

Read:
http://drupal.org/node/555562 (main message)
http://drupal.org/node/555648

I don't see why PostgreSQL would not be able to run queries like:

DELETE h
FROM history AS h
INNER JOIN term_node AS tn ON (h.nid = tn.nid)
INNER JOIN term_data AS td ON (td.tid = tn.tid)
WHERE h.uid = 2067 AND td.vid = 2

Ultimately, why not allow:

DELETE h, tn
FROM history AS h
INNER JOIN term_node AS tn ON (h.nid = tn.nid)
INNER JOIN term_data AS td ON (td.tid = tn.tid)
WHERE h.uid = 2067 AND td.vid = 2

IMHO this would improve compliance towards other database systems. To me
this seems to be in the reasonable scope of compatibility.

Kind regards,
Jean-Michel

Search Discussions

  • Bruce Momjian at Aug 22, 2009 at 3:17 pm
    Jean-Michel Pouré wrote:
    -- Start of PGP signed section.
    Dear Friends,

    First, thank you very much for considering a fix on the GROUP BY issue.
    I am starting a new thread about another issue:

    It seems that DELETE cannot understand INNER JOINS and needs HAVING.

    Read:
    http://drupal.org/node/555562 (main message)
    http://drupal.org/node/555648

    I don't see why PostgreSQL would not be able to run queries like:

    DELETE h
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    Ultimately, why not allow:

    DELETE h, tn
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    IMHO this would improve compliance towards other database systems. To me
    this seems to be in the reasonable scope of compatibility.
    Which "other database systems"? Only MySQL? If it is MySQL-only, we
    are unlikely to add it.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + If your life is a hard drive, Christ can be your backup. +
  • Alvaro Herrera at Aug 24, 2009 at 3:42 pm

    Bruce Momjian wrote:

    Ultimately, why not allow:

    DELETE h, tn
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    IMHO this would improve compliance towards other database systems. To me
    this seems to be in the reasonable scope of compatibility.
    Which "other database systems"? Only MySQL? If it is MySQL-only, we
    are unlikely to add it.
    The SQL standard does not support this syntax. They would have you put
    the joins in a subselect (which is often not enough because then you
    can't use outer joins).

    --
    Alvaro Herrera http://www.CommandPrompt.com/
    The PostgreSQL Company - Command Prompt, Inc.
  • Bruce Momjian at Aug 24, 2009 at 4:03 pm

    Alvaro Herrera wrote:
    Bruce Momjian wrote:
    Ultimately, why not allow:

    DELETE h, tn
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    IMHO this would improve compliance towards other database systems. To me
    this seems to be in the reasonable scope of compatibility.
    Which "other database systems"? Only MySQL? If it is MySQL-only, we
    are unlikely to add it.
    The SQL standard does not support this syntax. They would have you put
    the joins in a subselect (which is often not enough because then you
    can't use outer joins).
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax? Can that be added?


    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + If your life is a hard drive, Christ can be your backup. +
  • Alvaro Herrera at Aug 24, 2009 at 4:15 pm

    Bruce Momjian wrote:
    Alvaro Herrera wrote:
    Bruce Momjian wrote:
    Ultimately, why not allow:

    DELETE h, tn
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    IMHO this would improve compliance towards other database systems. To me
    this seems to be in the reasonable scope of compatibility.
    Which "other database systems"? Only MySQL? If it is MySQL-only, we
    are unlikely to add it.
    The SQL standard does not support this syntax. They would have you put
    the joins in a subselect (which is often not enough because then you
    can't use outer joins).
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax? Can that be added?
    Not sure about that. USING is already an extension to the standard, so
    if we extend it a bit more, it can't be a problem, can it? But this
    doesn't solve Jean Michel's problem, because MySQL does not support
    DELETE USING (or does it?).

    --
    Alvaro Herrera http://www.CommandPrompt.com/
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Bruce Momjian at Aug 24, 2009 at 4:23 pm

    Alvaro Herrera wrote:
    Bruce Momjian wrote:
    Alvaro Herrera wrote:
    Bruce Momjian wrote:
    Ultimately, why not allow:

    DELETE h, tn
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    IMHO this would improve compliance towards other database systems. To me
    this seems to be in the reasonable scope of compatibility.
    Which "other database systems"? Only MySQL? If it is MySQL-only, we
    are unlikely to add it.
    The SQL standard does not support this syntax. They would have you put
    the joins in a subselect (which is often not enough because then you
    can't use outer joins).
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax? Can that be added?
    Not sure about that. USING is already an extension to the standard, so
    if we extend it a bit more, it can't be a problem, can it? But this
    doesn't solve Jean Michel's problem, because MySQL does not support
    DELETE USING (or does it?).
    Right, but if we support ANSI joins in the USING clause, at least we
    would have a _functional_ equivalent, which we don't know because of
    missing outer join support.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + If your life is a hard drive, Christ can be your backup. +
  • Tom Lane at Aug 24, 2009 at 4:50 pm

    Alvaro Herrera writes:
    Bruce Momjian wrote:
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax? Can that be added?
    Not sure about that. USING is already an extension to the standard, so
    if we extend it a bit more, it can't be a problem, can it?
    I don't see any very good way to extend the USING syntax to allow the
    target table to be outer-joined to something else. Some other systems
    allow it by letting you re-specify the target in the other clause,
    equivalently to

    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.

    regards, tom lane
  • Robert Haas at Aug 24, 2009 at 5:18 pm

    2009/8/24 Tom Lane <tgl@sss.pgh.pa.us>:
    Alvaro Herrera <alvherre@commandprompt.com> writes:
    Bruce Momjian wrote:
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax?  Can that be added?
    Not sure about that.  USING is already an extension to the standard, so
    if we extend it a bit more, it can't be a problem, can it?
    I don't see any very good way to extend the USING syntax to allow the
    target table to be outer-joined to something else.  Some other systems
    allow it by letting you re-specify the target in the other clause,
    equivalently to

    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.
    I'm all in favor of compatibility, but if there is any way to make
    this work without massive collateral damage, I am also all in favor of
    that. I am forever writing queries that contain a needless self-join
    to work around the impossibility of directly outer-joining against the
    target.

    ...Robert
  • Tom Lane at Aug 24, 2009 at 5:41 pm

    Robert Haas writes:
    2009/8/24 Tom Lane <tgl@sss.pgh.pa.us>:
    ... Some other systems
    allow it by letting you re-specify the target in the other clause,
    equivalently to

    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.
    I'm all in favor of compatibility, but if there is any way to make
    this work without massive collateral damage, I am also all in favor of
    that. I am forever writing queries that contain a needless self-join
    to work around the impossibility of directly outer-joining against the
    target.
    It'd be pretty easy to do if we were willing to introduce a new reserved
    word; for example

    DELETE FROM target t USING SELF LEFT JOIN other_table ot ON ...

    (or maybe TARGET instead of SELF, or some other word). Wouldn't do
    anything for exact compatibility with MySQL or anybody else using the
    respecify-the-target-table-name approach. But it would be unambiguous
    and backwards-compatible. The real problem with this is that all the
    good candidates for the reserved word are things people are probably
    already using as aliases, so we'd have a large risk of breaking existing
    queries. We could avoid that with a sufficiently ugly choice like

    DELETE FROM target t USING DELETE_TARGET LEFT JOIN other_table ot ON ...

    but yech ...

    regards, tom lane
  • Sam Mason at Aug 24, 2009 at 5:52 pm

    On Mon, Aug 24, 2009 at 01:41:28PM -0400, Tom Lane wrote:
    The real problem with this is that all the
    good candidates for the reserved word are things people are probably
    already using as aliases, so we'd have a large risk of breaking existing
    queries. We could avoid that with a sufficiently ugly choice like

    DELETE FROM target t USING DELETE_TARGET LEFT JOIN other_table ot ON ...

    but yech ...
    PRIMARY or TABLE?

    Both are pretty grim, but I think they're reserved at the moment.
  • Bruce Momjian at Aug 24, 2009 at 9:34 pm

    Tom Lane wrote:
    Alvaro Herrera <alvherre@commandprompt.com> writes:
    Bruce Momjian wrote:
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax? Can that be added?
    Not sure about that. USING is already an extension to the standard, so
    if we extend it a bit more, it can't be a problem, can it?
    I don't see any very good way to extend the USING syntax to allow the
    target table to be outer-joined to something else. Some other systems
    allow it by letting you re-specify the target in the other clause,
    equivalently to

    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.
    Let's look at this a little closer. We can use an alias in the DELETE
    FROM clause:

    test=> DELETE FROM test t;

    test=> DELETE FROM test t USING test;

    What we cannot currently do is reference test twice:

    test=> DELETE FROM test USING test;
    ERROR: table name "test" specified more than once

    test=> DELETE FROM test t USING test t;
    ERROR: table name "t" specified more than once

    As far as I understand it, allowing ANSI joins in USING would simple
    mean removing that error message and linking the two table aliases.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + If your life is a hard drive, Christ can be your backup. +
  • Tom Lane at Aug 24, 2009 at 11:55 pm

    Bruce Momjian writes:
    What we cannot currently do is reference test twice:
    test=> DELETE FROM test USING test;
    ERROR: table name "test" specified more than once
    test=> DELETE FROM test t USING test t;
    ERROR: table name "t" specified more than once
    Hmm, I had forgotten that we throw errors in these cases now.
    Maybe that *would* give us an escape-hatch for the other interpretation.
    As far as I understand it, allowing ANSI joins in USING would simple
    mean removing that error message and linking the two table aliases.
    Well, you'd still need to complain about

    DELETE FROM test USING test JOIN test ON ...

    Also, it's not nearly as easy as just removing the error check.
    There's stuff in the planner (and perhaps executor) that's dependent on
    the assumption that the target table isn't on the inside of an outer
    join, for example. Still, getting agreement on a syntax would in itself
    be a huge step forward.

    regards, tom lane
  • Josh Berkus at Aug 25, 2009 at 2:37 am
    All,
    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.
    What I don't get is why this is such a usability issue. Subqueries in
    DELETE FROM work perfectly well, and provide more flexibility than most
    users know what to do with.

    Personally, I'd be happy just to stop with the SQL extension we have. I
    think extending USING any further is going to cause more problems than
    it solves.

    --
    Josh Berkus
    PostgreSQL Experts Inc.
    www.pgexperts.com
  • Bruce Momjian at Aug 25, 2009 at 1:02 pm

    Josh Berkus wrote:
    All,
    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.
    What I don't get is why this is such a usability issue. Subqueries in
    DELETE FROM work perfectly well, and provide more flexibility than most
    users know what to do with.
    OK, so you are saying that every OUTER join can be efficiently
    reprsented as a subquery? If that is true we don't need to add ANSI
    join support to USING.

    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + If your life is a hard drive, Christ can be your backup. +
  • Robert Haas at Aug 25, 2009 at 1:24 pm

    On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkuswrote:
    All,
    DELETE FROM target t USING t LEFT JOIN other_table ot ON ...

    but we have always considered that the target is *not* to be identified
    with any member of the FROM/USING clause, so it would be a serious
    compatibility break to change that now.
    What I don't get is why this is such a usability issue.  Subqueries in
    DELETE FROM work perfectly well, and provide more flexibility than most
    users know what to do with.

    Personally, I'd be happy just to stop with the SQL extension we have.  I
    think extending USING any further is going to cause more problems than
    it solves.
    It's both a usability issue and a performance issue. Suppose you want
    to select all the rows in foo whose id field does not appear in
    bar.foo_id. The most efficient way to do this in PostgreSQL is
    typically:

    SELECT foo.* FROM foo LEFT JOIN bar ON foo.id = bar.foo_id WHERE
    bar.foo_id IS NULL;

    Now, if you want to delete those rows, you can't do it without an
    extra join somewhere. You can do it like this:

    DELETE FROM foo AS foo1
    USING foo AS foo2 LEFT JOIN bar ON foo2.id = bar.foo_id
    WHERE foo1.id = foo2.id AND foo2;

    Or like this:

    DELETE FROM foo WHERE id IN (SELECT foo.id FROM foo LEFT JOIN bar ON
    foo.id = bar.foo_id WHERE bar.foo_id IS NULL);

    ...but either way you now have foo in there twice when it really
    shouldn't need to be, and you're doing a useless self-join to work
    around a syntax limitation.

    [ thinks ]

    Actually, I guess in this case you can get around it like this:

    DELETE FROM foo WHERE NOT EXISTS (SELECT 1 FROM bar WHERE bar.foo_id = foo.id);

    ...but I'm not sure it can be rewritten that way in every case - in
    particular, that won't work if you have a RETURNING clause that
    includes a value taken from bar.

    ...Robert
  • Tom Lane at Aug 25, 2009 at 1:50 pm

    Robert Haas writes:
    On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkuswrote:
    What I don't get is why this is such a usability issue.  Subqueries in
    DELETE FROM work perfectly well, and provide more flexibility than most
    users know what to do with.
    It's both a usability issue and a performance issue.
    On the usability front: if we were to take the position Josh advocates,
    we should never have added FROM/USING to UPDATE/DELETE at all ... but
    since we did, I think we should try to make it as flexible as the
    corresponding feature in other DBMSes.

    On the performance front: yeah, you can recast most joins as subqueries,
    but you tend to end up with the equivalent of a nestloop plan. Works
    okay for small numbers of rows, scales horribly.

    regards, tom lane
  • Andrew Dunstan at Aug 25, 2009 at 1:58 pm

    Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkuswrote:
    What I don't get is why this is such a usability issue. Subqueries in
    DELETE FROM work perfectly well, and provide more flexibility than most
    users know what to do with.
    It's both a usability issue and a performance issue.
    On the usability front: if we were to take the position Josh advocates,
    we should never have added FROM/USING to UPDATE/DELETE at all

    FWIW, I use update ... from a lot - it make many update queries easier
    and less error prone to write - but I rarely find I need delete ... using.

    ... but
    since we did, I think we should try to make it as flexible as the
    corresponding feature in other DBMSes.

    +1

    cheers

    andrew
  • Greg Stark at Aug 25, 2009 at 2:08 pm

    On Tue, Aug 25, 2009 at 2:50 PM, Tom Lanewrote:
    On the performance front: yeah, you can recast most joins as subqueries,
    but you tend to end up with the equivalent of a nestloop plan.  Works
    okay for small numbers of rows, scales horribly.
    Well that's our problem isn't it? I thought we were capable of
    genearting semijoins for subqueries these days though?

    The problem I thought was if you wanted to pull multiple values out of
    the subquery.

    So something like

    UPDATE foo set a=aa, b=bb FROM bar WHERE ...

    If you wanted to do an outer join from foo to bar then how would you
    write it as an subquery even if our optimizer could notice the
    semijoin and optimize it properly?

    You would have to write something like

    UPDATE foo set a = (select aa from bar where...)
    b = (select bb from bar where...)

    and then the optimizer would have to notice the duplicates and
    consolidate them? That seems inconvenient (and fragile).
  • Tom Lane at Aug 25, 2009 at 2:20 pm

    Greg Stark writes:
    You would have to write something like
    UPDATE foo set a = (select aa from bar where...)
    b = (select bb from bar where...)
    and then the optimizer would have to notice the duplicates and
    consolidate them? That seems inconvenient (and fragile).
    Well, that's why the spec nowadays allows you to write

    UPDATE foo SET (a,b) = (select aa,bb from bar where ...)

    But we haven't got that, and if we did it would generate a nestloop
    plan. Getting to the point of absolute performance equivalence between
    subqueries and joins would take a *lot* of work; I'm not even sure it's
    possible at all. And once we'd done all that work there would still
    remain the fact that people are accustomed to using join syntax instead.
    There's a lot of existing code out there that would be a lot easier
    to port to PG if we supported that style (which was exactly the point
    made by the OP).

    regards, tom lane
  • Bill Moran at Aug 24, 2009 at 4:29 pm

    In response to Bruce Momjian <bruce@momjian.us>:

    Alvaro Herrera wrote:
    Bruce Momjian wrote:
    Ultimately, why not allow:

    DELETE h, tn
    FROM history AS h
    INNER JOIN term_node AS tn ON (h.nid = tn.nid)
    INNER JOIN term_data AS td ON (td.tid = tn.tid)
    WHERE h.uid = 2067 AND td.vid = 2

    IMHO this would improve compliance towards other database systems. To me
    this seems to be in the reasonable scope of compatibility.
    Which "other database systems"? Only MySQL? If it is MySQL-only, we
    are unlikely to add it.
    The SQL standard does not support this syntax. They would have you put
    the joins in a subselect (which is often not enough because then you
    can't use outer joins).
    So the problem is that our DELETE ... USING does not allow ANSI join
    syntax? Can that be added?
    I suspect that the reason MySQL has this syntax is because for a long time
    they didn't have proper foreign keys and referential integrity.

    With proper foreign keys and ON DELETE CASCADE, why would supporting
    such syntax even be necessary?
  • Jean-Michel Pouré at Aug 25, 2009 at 7:09 am

    With proper foreign keys and ON DELETE CASCADE, why would supporting
    such syntax even be necessary?
    Porting existing abstraction layers from ANSI JOINs to ON DELETE CASCADE
    is complicated.
    What I don't get is why this is such a usability issue. Subqueries in
    DELETE FROM work perfectly well, and provide more flexibility than
    most
    users know what to do with.
    The ANSI syntax allows deleting one or several tables at once.
    Subqueries are not supported by MySQL on DELETE.

    Again, this is a usability issue to gain market shares and happy users
    againts MySQL.

    Kind regards,
    Jean-Michel

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 22, '09 at 7:40a
activeAug 25, '09 at 2:20p
posts21
users10
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase