Re: Need LIMIT and ORDER BY for UPDATE - Mailing list pgsql-general

John D. Burger wrote:
> D. Dante Lorenso wrote:
>
>> 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.
>
>> Can something like what I want be added in a future version?  Ideas or
>> alternatives?  I don't see how I can rewrite this query as a single
>> statement any other way and get the same expectations.
>
> Doesn't this do it, assuming invoice_id is unique?
>
>   UPDATE invoice
>   SET reserve_ts = NOW() + '1 hour'::timestamp
>   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)
>   RETURNING invoice_id;

Doesn't this create race condition in the query where multiple processes
might find the same invoice_id while executing the inner select.  The
update would then update the same record more than once during the
update step and 2 processes might get the same invoice_id returned.  In
otherwords, moving the select criteria into a sub-query breaks the
atomic nature of the update.  Right?

I have been trying to doing something like this, though:

   UPDATE invoice
   SET reserve_ts = NOW() + '1 hour'::timestamp
   WHERE reserve_ts < NOW()
   AND 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
   )
   RETURNING invoice_id;

By checking the reserve_ts inside the SELECT and again inside the UPDATE
this should catch the race condition and only allow one process to
perform the update on a given match.  If the other process has updated
the reserve_ts already, the reserve_ts would not pass the second check.
  However, the new side-effect is that one process would receive a NULL
return result when the race condition occurs rather than just picking up
the next queue invoice_id.

Unless I can get what I really want, this will have to do, I suppose.

-- Dante