[DBIx-Class-Devel] UPDATE RETURNING

[Peter Rabbitson]

On Mon, Jul 15, 2013 at 10:46:55AM -0500, fREW Schmidt wrote:
> On Mon, Jul 15, 2013 at 03:25:46PM +0000, Peter Rabbitson wrote:
> > On Mon, Jul 15, 2013 at 10:13:11AM -0500, fREW Schmidt wrote:
> > > I just found out about UPDATE RETURNING (and the various dialects
> > > thereof, of course) and I'm aching to use it in DBIC.  What do you
> > > guys think about something like this?
> > >
> > > my @rows = $rs->update({ .... }, { columns => $columns_spec })->all;
> >
> > What is the actual use-case here? I understand what the construct does,
> > I am not sure how it is useful in practice.
> >
> 
> At work we maintain a table of devices and when they last checked in to
> the server.  If they haven't checked in recently enough (aka timed
> out) we do a special action.  Currently the way it works is, we have a
> single update that inserts our PID into the row's status if it has
> timed out, and then we iterate over the rows with our PID in the
> status, doing the special action as we go.  With UPDATE RETURNING this
> becomes a single "select" as we can get back the rows (or just their
> id's) in a single statement.  Basically it increases atomicity. 

This is a very contrived use-case (I would simply solve it with batches 
wrapped in transactions), and I don't think it warrants a full blown 
chunk of the scrace API. You kind-of agree further down.

> FWIW
> I could see it being very useful for shadow, but only with SQL Server
> as SQL Server's dialect can give you the values before UPDATE (pg
> gives the values after exclusively)

It won't be - I actually need the stuff post-RDBMS-side update, not 
before. INSERT and UPDATE record the state *after* each corresponding 
operation, only DELETE records the state before (due to ability of 
RDBMS-side cascades to disappear my relationship data). Remember it's 
not the snapshots that are hard, it is keeping track of the proper link 
between snapshots.

> 
> > > It should be able to use select/as too, fwiw.
> >
> > I am not sure what this comment implies...
> 
> $rs->update(..., { select => ..., as => ... })

I may misremember, but I think you can only return stored values, no 
aggreegates/subselects.

> 
> > > The other obvious and safe use case is to add another thing to the column
> > > definitions so that when a user calls $row->update the row object can
> > > have it's guts automatically updated.
> >
> > This is quote separate and something that can be cleanly folded in (and
> > be emulated on lesser RDBMS) - see the current implementation of
> > retrieve_on_insert. I would not mind the column attribute to be extended
> > with a retrieve_on_update boolean.
> >
> 
> Alright, so maybe the TL;DR for now is, we build this for just rows
> for starters, making reasonably good support for it, and then if we
> make the API useful enough I could make either a helper or an
> update_returning method in core that can give me what I want.

Something like that.

> As for my "lazy" comment, imagine we supported the following:
> 
>    say $_->name for $rs->search({
>       id => {
>          -in => $rs->update_returning({
>             foo => 'bar'
>          }, {
>             columns => 'id', # or maybe better yet ->get_column('id')
>          })->as_query
>       }
>    })->all
> 
> So update_returning returns an unfetched resultset, just like search
> does.

I don't think many engines support this (may very well be Pg only, 
verify) - but afaik RETURNING does not yield to being treated as a 
subselect. That is its values are only available to be shoved in a 
cursor.