RE: READ COMMITTED vs. index-only scans

Please remove me from this list. Thanks.

Karen Stone| Technical Services| Eldorado |a Division of MphasiS
5353 North 16th Street, Suite 400, Phoenix, Arizona 85016-3228
Tel (928) 892 5735 | www.eldoinc.com | www.mphasis.com |kstone(at)eldocomp(dot)com

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, January 17, 2018 11:56 AM
To: Jacek Kołodziej <kolodziejj(at)gmail(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: READ COMMITTED vs. index-only scans

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

Can you determine whether the row(s) missing in the second query are freshly committed? Or have they been there awhile?

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

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