Re: Update with subselect sometimes returns wrong result

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...

To get rid of that ambiguity, I suggest rewriting the query to look
like:
WITH locked_row AS (
SELECT id FROM t1 LIMIT 1 FOR UPDATE
)
UPDATE t1 SET id = t1.id
FROM (SELECT * FROM locked_row) locked
WHERE t1.id = locked.id
RETURNING t1.id;

that should always be safe and indeed, I cannot reproduce the problem
that way.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services