"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. -- Bill Moran http://www.potentialtech.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
