Re: Need LIMIT and ORDER BY for UPDATE

Bill Moran wrote:
> "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.

   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.

-- Dante