Re: Need LIMIT and ORDER BY for UPDATE

"D. Dante Lorenso" <dante@lorenso.com> wrote:
>
> All,
>
> I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
> commands.  Is this possible?
>
>    UPDATE invoice i
>    SET reserve_ts = NOW() + '1 hour'::timestamp
>    FROM account a
>    WHERE a.acct_id = i.acct_id
>    AND i.reserve_ts < NOW()
>    AND a.status = 'A'
>    AND i.is_paid IS FALSE
>    ORDER BY i.create_ts ASC
>    LIMIT 1
>    RETURNING invoice_id;
>
> This query would find JUST ONE invoice record which is not paid and
> reserve the right to operate on the row using the 'reserve_ts' column
> for all active accounts.  The one row would be the oldest invoice
> matching the criteria.  Only that one row would be updated and the
> invoice_id of the updated row (if any) would be returned.
>
> Running a query like this over and over would pop just one record off
> the queue and would guarantee an atomic reservation.

While I'm not going to argue as to whether your suggestion would be
a good idea or not, I will suggest you look at SELECT FOR UPDATE, which
will allow you to do what you desire.

--
Bill Moran
http://www.potentialtech.com