Re: BUG #10189: Limit in 9.3.4 no longer works when ordering using a composite multi-type index

On 2014-05-02 14:23:50 -0400, Tom Lane wrote:
> Nick Rupley <nickr(at)mirthcorp(dot)com> writes:
> > We also tracked down every single duplicate entry and noted the time it got
> > inserted. What we found is that this issue only ever started happening
> > after we upgraded from 9.0.13 to 9.3.4. The upgrade itself doesn't seem to
> > be the culprit, but messages received *after* the upgrade encountered this
> > issue.
>
> Oh dear. You were never on any earlier 9.3.x release? If not, then
> there's still some unfixed bug in this area in 9.3.4.
>
> There's been one post-9.3.4 fix in this same general area:
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c0bd128c8
> but according to the commit message, at least, that bug would not have led
> to the symptom you're seeing, namely rows disappearing from indexes while
> they're still visible to seqscans.

Hm. With a bit of bad luck it might. The bug essentially has the
consequence that two updates might succeed for the same row. Consider
what happens if the row gets hot updated and then a second hot update,
due to the bug, also succeeds. The second update will change t_ctid of
the old tuple to point to the second version. If the transaction that
did the second update then aborts a index search starting at the root of
the hot change won't find any surviving tuple. But a seqscan will. :(.

Greetings,

Andres Freund

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