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.
Similar syntax would be very useful for DELETE operations. The idea is
that doing an UPDATE with RETURNING allows a get/set operation in a
single query. Without the LIMIT and ORDER BY, I'm forced to reserve all
rows at once which my application doesn't want to handle like that.
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.
-- Dante
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
