Re: Update with subselect sometimes returns wrong result

Andres Freund-3 wrote
> Hi,
>
> On 2013-11-30 00:08:14 +0100, Oliver Seemann wrote:
>> Then the following UPDATE should return exactly one row:
>>
>> UPDATE t1 SET id = t1.id
>> FROM (SELECT id FROM t1 LIMIT 1 FOR UPDATE) AS subset
>> WHERE t1.id = subset.id
>> RETURNING t1.id
>
> It turns out, this currently (as Tom points out) is a question of how
> the query is planned. UPDATEs with a FROM essentially are a join between
> the involved tables. Roughly, this query can either be planned as
> a) Scan all rows in subset, check whether it matches a row in t1.
> or
> b) Scan all rows in t1, check for each whether it matches a row in subset.
>
> a) is perfectly fine for what you want, it will only return one row. But
> b) is problematic since it will execute the subselect multiple
> times, once for each row in t1. "FOR locklevel" currently has the property
> of ignoring rows that the current command has modified, so you'll always
> get a different row back...

From the earlier previously referenced thread it appears a WHERE IN
(sub-select limit for update) clause exhibits the same behavior.

Can we make it so that option B is never considered a valid plan if the join
target has a limit (and/or the for update, depending) applied?

Also, why does B execute the sub-select multiple times?  I would think it
would only do that if the sub-query was correlated. The non-correlated
sub-query should conceptually create a CTE on-the-fly and not require the
caller to do so manually.

While the entire from/where section is inherently correlated due to the
joining the fact that the from's table reference is a query and not a simple
relation means there are effectively two levels to consider.

If you really need option B you can write a correlated exists sub-query
which implies a limit 1 which solves the "sub-query relation is much larger
than the from relation so I want to scan the from relation first"
requirement.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Update-with-subselect-sometimes-returns-wrong-result-tp5780925p5781081.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.