From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
---|---|
To: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need LIMIT and ORDER BY for UPDATE |
Date: | 2007-12-13 04:43:19 |
Message-ID: | 20071212234319.ac7f872b.wmoran@potentialtech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"D. Dante Lorenso" <dante(at)lorenso(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | John D. Burger | 2007-12-13 04:48:06 | Re: Need LIMIT and ORDER BY for UPDATE |
Previous Message | robert | 2007-12-13 04:09:44 | mssql migration and boolean to integer problems |