Re: Update with subselect sometimes returns wrong result - Mailing list pgsql-bugs

I wrote:
> Anyway, at this point I'm not so much wondering why it fails as why it
> (seems to) work *any* of the time.  And how is it that VACUUM sometimes
> manages to flip it from working state to not-working state?  (Once you're
> in the state where the UPDATE will say it updated two rows, it's 100%
> reproducible.)

Oh, hah; the case where it works is where the generated plan is the other
way around:

 Update on t1
   ->  Nested Loop
         Join Filter: (t1.id = subset.id)
         ->  Subquery Scan on subset
               ->  Limit
                     ->  LockRows
                           ->  Seq Scan on t1 t1_1
         ->  Seq Scan on t1

so that we never rescan the LockRows node.  heap_lock_tuple followed by
heap_update works sanely, the other way round not so much.

The apparent dependency on VACUUM is probably coming from updating the
table's relpages/reltuples counts to new values in a way that causes the
planner to think one version or the other is a bit cheaper.

I'd still kind of like to know how HEAP_XMAX_IS_MULTI is getting involved,
but it seems that the fundamental problem here is we haven't thought
through what the interactions of LockRows and ModifyTable operations in
the same query ought to be.

            regards, tom lane