Bill Moran wrote:
"D. Dante Lorenso" <dante@xxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
