[DBIx-Class-Devel] UPDATE RETURNING

[fREW Schmidt]

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.  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 should be able to use select/as too, fwiw.
>
> I am not sure what this comment implies...

$rs->update(..., { select => ..., as => ... })

> > 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.

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.

--
fREW Schmidt
http://blog.afoolishmanifesto.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20130715/51fc61ad/attachment.pgp