Re: LIMIT for UPDATE and DELETE

Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> (2014/08/15 6:18), Rukh Meski wrote:
>> Based on the feedback on my previous patch, I've separated only the
>> LIMIT part into its own feature.  This version plays nicely with
>> inheritance.  The intended use is splitting up big UPDATEs and DELETEs
>> into batches more easily and efficiently.
>
> Before looking into the patch, I'd like to know the use cases in more
> details.

There have been a few times I wanted something like this, so that
it was easier to do an update that affects a very high percentage
of rows in a table, while making the old version of the row no
longer match the selection criteria for the UPDATE.  There are
workarounds using cursors or subselects returning ctid, but they
are kludgy and error prone.  Basically I wanted to alternate UPDATE
of a subset of the rows with table VACUUM so that subsequent
iterations can re-use space and avoid bloating the table.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company