Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

On Tue, Jun 24, 2014 at 04:08 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> IMHO this needs to work with inheritance if we are to accept it. It would be
> a rather strange limitation for no apparent reason, other than that we
> didn't bother to implement it. It doesn't seem very difficult in theory to
> add the table OID to the plan as a junk column, and use that in the
> ModifyTable node to know which table a row came from.

I can have a go at that, but I'm somewhat afraid of the performance
implications this might have. And it's not just users of this feature
that would pay the penalty, it would be everyone.

> Per the docs in the patch:
>
>> + <para>
>> + If the <literal>LIMIT</> (or <literal>FETCH FIRST</>) clause
>> + is present, processing will stop after the system has attempted
>> + to delete the specified amount of rows. In particular, if a row
>> + was concurrently changed not to match the given <literal>WHERE</>
>> + clause, it will count towards the <literal>LIMIT</> despite it
>> + not being actually deleted. Unlike in <literal>SELECT</>, the
>> + <literal>OFFSET</literal> clause is not available in
>> + <literal>DELETE</>.
>> + </para>
>
>
> That behavior with READ COMMITTED mode and concurrent changes is surprising.
> Do we really want it to behave like that, and if so, why?

Oh, oops. Looks like I didn't submit the latest version of the patch
for the commit fest, where I had fixed the documentation. It doesn't
work that way anymore, as we really don't want it to work that way.

> Why is OFFSET not supported? Not that I care much for that, but I'm curious.

I thought it seemed weird. But it's supported for FOR UPDATE, so
maybe we should support it here as well.

♜