Horton
Menu

Thread

  1. Expanding DELETE/UPDATE returning

    Rusty Conover <rconover@infogears.com> — 2007-02-27T03:24:20Z

    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.
Web: http://www.infogears.com

  2. Re: Expanding DELETE/UPDATE returning

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-02-27T04:14:01Z

    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

  3. Re: Expanding DELETE/UPDATE returning

    Jim Nasby <jim@nasby.net> — 2007-02-27T05:12:44Z

    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)

  4. Re: Expanding DELETE/UPDATE returning

    David Fetter <david@fetter.org> — 2007-02-27T05:24:45Z

    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

  5. Re: Expanding DELETE/UPDATE returning

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-02-27T05:30:57Z

    "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

  6. Re: Expanding DELETE/UPDATE returning

    Florian Pflug <fgp@phlo.org> — 2007-02-27T14:07:06Z

    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

  7. Re: Expanding DELETE/UPDATE returning

    Bruno Wolff III <bruno@wolff.to> — 2007-03-02T18:47:21Z

    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.