Thread: Expanding DELETE/UPDATE returning
Hi,
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? Or allow delete and update to be used in sub-queries?
Some examples:
create temp table t1 (id integer, location_id integer);
insert into t1 values(1, 1);
insert into t1 values(2, 1);
insert into t1 values(3, 1);
insert into t1 values(1, 3);
create temp table search_requests (id integer);
insert into search_requests values(1);
insert into search_requests values(3);
-- This works
delete from search_requests using t1 where t1.id = search_requests.id returning t1.id, t1.location_id;
-- This does not work.
delete from search_requests using t1 where t1.id = search_requests.id returning t1.id, t1.location_id order by t1.location_id desc;
-- This does not work.
select foo.id, count(*) from (delete from search_requests using t1 where t1.id = search_requests.id returning t1.id, t1.location_id) as foo group by foo.id;
Thanks,
Rusty
--
Rusty Conover
InfoGears Inc.
Rusty Conover <rconover@infogears.com> 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
On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: > Rusty Conover <rconover@infogears.com> 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? <brain_storm> 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 <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: > Rusty Conover <rconover@infogears.com> 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 jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
"Jim C. Nasby" <jim@nasby.net> writes:
> On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote:
>> Rusty Conover <rconover@infogears.com> 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
David Fetter wrote: > On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: >> Rusty Conover <rconover@infogears.com> 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? > > <brain_storm> > > 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
On Tue, Feb 27, 2007 at 15:07:06 +0100, "Florian G. Pflug" <fgp@phlo.org> 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.