Re: READ COMMITTED vs. index-only scans

Hi Tom,

On Wed, Jan 17, 2018 at 7:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?Jacek_Ko=C5=82odziej?= <kolodziejj(at)gmail(dot)com> writes:
> > Here's what happening to me: the "A" query occasionally (in my case: on
> the
> > order of tenths per day) returns an ID _higher_ than any ID present in
> > second query's result (other conditions I haven't specified do _not_
> filter
> > any more rows than "id <= max ID") - as if some entries were visible for
> > the first query, but not for the second one. This is an inconsistency
> that
> > is very problematic for me.
>
> That sounds problematic to me too, but how certain are you that the "other
> conditions you haven't specified" aren't suppressing the last row? That'd
> certainly be the least surprising explanation. If it isn't that, though,
> this surely seems like a bug.
>
> Yes, I'm fairly sure of that. When I execute that same "B" query again
some time afterwards, it returns all expected rows - I mean, also these
that were "included" in original "A" query and that were "missing" in "B"
one first time around.

> Can you determine whether the row(s) missing in the second query are
> freshly committed? Or have they been there awhile?
>
> Depends on what would be considered "fresh", usually it's on the order of
miliseconds or seconds.

> > Where am I wrong? What am I missing? What information may I provide to
> help
> > with investigating this?
>
> Probably the best thing to spend time on would be to try to extract a
> publishable test case. It would be really hard to get to the bottom
> of an issue like this without having a reproducer. It's okay if it
> takes awhile to reproduce the fault ...
>
> I'd certainly love to have a working repro. I won't be able to do it for
the next few days but I'll work on this right after the weekend.

> Also, before spending a whole lot of time on this: are you on 9.6.6?
> If not, update, just in case this is an already-fixed issue. The
> symptoms don't sound familiar, but I don't want to waste a lot of
> time only to find out it's some manifestation of a known bug.
>
> regards, tom lane
>

I'm using 9.6.5; I'm not administrating it so it might take some time
before updating but once it's done, I'll get back with whether that fixed
the situation. In the meantime, when trying to reproduce it locally, I'll
use both 9.6.5 and 9.6.6 to see whether it makes any difference.

Thank you very much for the suggestions.

--
Kind regards,
Jacek Kołodziej
http://kolodziejj.info