Re: DELETE and UPDATE with LIMIT and ORDER BY

On Tue, Apr 25, 2017 at 2:42 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Mon, Apr 24, 2017 at 8:09 AM, Surafel Temesgen <surafel3000(at)gmail(dot)com>
> wrote:
>>
>> the necessity of allowing limit and order by clause to be used with delete
>> and
>> update statement is discussed in the past and added to the todo list
>>
>> preveouse mailing list descissions
>>
>> http://archives.postgresql.org/pgadmin-hackers/2010-04/msg00078.php
>> http://archives.postgresql.org/pgsql-hackers/2010-11/msg01997.php
>
>
> See this more recent one:
>
> https://www.postgresql.org/message-id/flat/54102581(dot)2020207%40joh(dot)to#54102581(dot)2020207(at)joh(dot)to
>
> That patch was not adopted, as I recall, mostly due to the requirement that
> it support partitioned tables.

+1. The discussion there applies to inheritance based as well as
declarative partitioning, although Tom Lane thought that declarative
partitioning would not require special handling.

IIUC, the patch is simply pushing the LIMIT down into the underlying
scan. If the scan returns LIMIT number of rows, but some of those rows
were changed by a concurrent update, such that the new version doesn't
fit the filters, it will end up updating less that LIMIT number of
rows. I think that's not expected I guess.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company