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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
