Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update

Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> Bernice, are there other
> related issues you're aware of beyond this rowmark bug? Want to make
> sure Dean's patch covers them too.

What I found was this takes a combination of nested loop and index
scan (or tidscan) to lose the update. The first update succeeds and
subsequent updates disappear. Nested loop with seqscan is fine, and so
is index scan with merge and hash joins. My beginner guess is that
IndexRecheck in EPQ is returning true on the first row, and then false
on subsequent rows (due to the missing rowmarks). SeqRecheck always
returns true.

explain update t set count = count + 1 from (values (1), (2)) v(id)
where t.id = v.id;

This loses updates:
Update on t (cost=0.15..16.38 rows=0 width=0)
-> Nested Loop (cost=0.15..16.38 rows=2 width=38)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32)
-> Index Scan using t_pkey on t (cost=0.15..8.17 rows=1 width=14)
Index Cond: (id = "*VALUES*".column1)

This doesn't:
Update on t (cost=0.20..90.61 rows=0 width=0)
-> Hash Join (cost=0.20..90.61 rows=2 width=38)
Hash Cond: (t.id = "*VALUES*".column1)
-> Index Scan using t_pkey on t (cost=0.15..82.06 rows=2260 width=14)
-> Hash (cost=0.03..0.03 rows=2 width=32)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32)

and neither does:
Update on t (cost=0.00..121.73 rows=0 width=0)
-> Nested Loop (cost=0.00..121.73 rows=2 width=38)
Join Filter: (t.id = "*VALUES*".column1)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32)
-> Seq Scan on t (cost=0.00..32.60 rows=2260 width=14)

Thanks, Bernice