Re: [HACKERS] Expanding DELETE/UPDATE returning
On Tue, Feb 27, 2007 at 15:07:06 +0100, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote: > > select * from t1, (delete from t2 returning t2.t1_id) where t1.id = > t2.t1_id limit 1 ; > > I for my part couldn't even say what I'd expect that query to do. I would expect it to delete all rows from t2 but only return 1 row as output. I think the ambiguous cases are going to come from cases where deleting some rows in a subquery changes which rows will be deleted in subsequent executions of the same subquery. Something like deleting the row with the least value for some column. ---(end of broadcast)--- TIP 1: 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] Expanding DELETE/UPDATE returning
David Fetter wrote: On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: Rusty Conover <[EMAIL PROTECTED]> writes: Or allow delete and update to be used in sub-queries? That's been discussed but the implementation effort seems far from trivial. One big problem is that a sub-query can normally be re-executed multiple times, eg on the inner side of a join; whereas that's clearly not acceptable for an insert/update/delete. What kinds of machinery would be needed in order for certain kinds of subqueries to get executed only once and have the results cached? INSERT/UPDATE/DELETE ... RETURNING wouldn't be the only possible uses of such machinery. A data-changing function in a subquery could be another. Maybe there could be some way to mark functions as "execute once per subquery." Is "execute only once" even well-defined for subqueries? Take for example select * from t1, (delete from t2 returning t2.t1_id) where t1.id = t2.t1_id ; Will this delete all record from t2, or just those records for which a matching record in t1 exists? In case you vote for "all records" above, now take select * from t1, (delete from t2 returning t2.t1_id) where t1.id = t2.t1_id limit 1 ; I for my part couldn't even say what I'd expect that query to do. Do other databases support this? greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Expanding DELETE/UPDATE returning
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: >> Rusty Conover <[EMAIL PROTECTED]> writes: >>> Or allow delete and update to be used in sub-queries? >> >> That's been discussed but the implementation effort seems far from >> trivial. One big problem is that a sub-query can normally be >> re-executed multiple times, eg on the inner side of a join; whereas >> that's clearly not acceptable for an insert/update/delete. > Couldn't we avoid that by writing the data to a tuplestore? Or is it too > hard to detect the cases when that would need to happen? I said it wasn't trivial, not that it wasn't doable. Offhand I think you'd need a tuplestore buffer, plus a major refactoring of the executor toplevel --- because currently, all types of updating queries are hardwired for the actual update to occur only at top level. There are also some definitional issues: when do you think triggers should fire? What happens if the outer query contains a LIMIT clause that a naive user would think causes the updating subquery not to be read all the way to the end? If there are multiple updating subqueries in the same outer query, how do they interact? I don't say it's insoluble, just not trivial ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Expanding DELETE/UPDATE returning
On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: > Rusty Conover <[EMAIL PROTECTED]> writes: > > I didn't see this on the TODO list, but if it is my apologies. Is it > > in the cards to expand the functionality of DELETE/UPDATE returning > > to be able to sort the output of the rows returned? > > No. > > > Or allow delete > > and update to be used in sub-queries? > > That's been discussed but the implementation effort seems far from > trivial. One big problem is that a sub-query can normally be > re-executed multiple times, eg on the inner side of a join; whereas > that's clearly not acceptable for an insert/update/delete. Couldn't we avoid that by writing the data to a tuplestore? Or is it too hard to detect the cases when that would need to happen? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Expanding DELETE/UPDATE returning
On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: > Rusty Conover <[EMAIL PROTECTED]> writes: > > I didn't see this on the TODO list, but if it is my apologies. Is > > it in the cards to expand the functionality of DELETE/UPDATE > > returning to be able to sort the output of the rows returned? > > No. Would this be something that windowing functions would need to take into account? > > Or allow delete and update to be used in sub-queries? > > That's been discussed but the implementation effort seems far from > trivial. One big problem is that a sub-query can normally be > re-executed multiple times, eg on the inner side of a join; whereas > that's clearly not acceptable for an insert/update/delete. What kinds of machinery would be needed in order for certain kinds of subqueries to get executed only once and have the results cached? INSERT/UPDATE/DELETE ... RETURNING wouldn't be the only possible uses of such machinery. A data-changing function in a subquery could be another. Maybe there could be some way to mark functions as "execute once per subquery." Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Expanding DELETE/UPDATE returning
Rusty Conover <[EMAIL PROTECTED]> writes: > I didn't see this on the TODO list, but if it is my apologies. Is it > in the cards to expand the functionality of DELETE/UPDATE returning > to be able to sort the output of the rows returned? No. > Or allow delete > and update to be used in sub-queries? That's been discussed but the implementation effort seems far from trivial. One big problem is that a sub-query can normally be re-executed multiple times, eg on the inner side of a join; whereas that's clearly not acceptable for an insert/update/delete. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
