Re: Need LIMIT and ORDER BY for UPDATE

In response to "D. Dante Lorenso" <dante(at)lorenso(dot)com>:
> Bill Moran wrote:
> > "D. Dante Lorenso" <dante(at)lorenso(dot)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.
>
> UPDATE invoice
> SET reserve_ts = NOW() + '1 hour'::interval
> WHERE invoice_id = (
> SELECT invoice_id
> FROM invoice i, 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
> FOR UPDATE
> )
> RETURNING invoice_id;
>
> Does this do the same thing while still remaining a single atomic query
> that will guarantee no race conditions during the inner select/update?
>
> ERROR: SELECT FOR UPDATE/SHARE is not allowed in subqueries
>
> Guess not.

BEGIN;
SELECT invoice_id
FROM invoice i, 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
FOR UPDATE;
UPDATE invoice
SET reserve_ts = NOW() + '1 hour'::interval
WHERE invoice_id = [previously selected value];
COMMIT;

And before you start asking a lot of "won't this x or y", please read
the docs:
http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE

Then feel free to ask more questions.

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