Re: READ COMMITTED vs. index-only scans - Mailing list pgsql-general

17.01.2018 3:30 PM "Melvin Davidson" <melvin6925@gmail.com> napisał(a):

On Wed, Jan 17, 2018 at 8:08 AM, Jacek Kołodziej <kolodziejj@gmail.com> wrote:

Hello everyone,

this is my first post here and I'm starting with asking a question about data consistency between two consecutive SELECTs using PostgreSQL 9.6.

I'm sorry if that's something that was already discussed - I couldn't find it either in archives, nor in _general internet_. If it is, I would appreciate pointing it out.

I have an "append-only" events table - only INSERT and SELECT queries are issued to it. It has an integer (sequence) ID as a primary key.

I'm issuing following two queries (within the same READ COMMITTED transaction) to that table:

- first query (A): SELECT id FROM events ORDER BY events.id DESC LIMIT 1 - I'm saving the result as a "max ID" for the second query

- second query (B): SELECT id, ... FROM events WHERE id > "min ID" AND id <= "max ID" AND ...

  - I won't dig into what's "min ID" but you can assume it's at most 100 less than "max ID"

Concurrently, rows are being added to that table.

Please note that there's enough data in the index for the first query (A) to perform an index-only scan. I'm not sure if that's relevant but "B" query does an index scan because of other conditions I haven't mentioned but still needs to fetch data from the table's heap.

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.

All I can think of is that it might be caused by the index-only-scan and READ COMMITTED transaction isolation level but from what I gather from documentation, it should not be possible due to the use of visibility map[0][1][2].

And yet it happens, likely for some other reason but I can't think of any. I've tried switching transaction isolation level to REPEATABLE READ (on the basis that it's an faulty phenomenon occurring during to some bug) but that didn't help.

Where am I wrong? What am I missing? What information may I provide to help with investigating this?

[0] https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html

[1] https://www.postgresql.org/docs/9.6/static/storage-vm.html

[2] https://wiki.postgresql.org/wiki/Index-only_scans

--

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

>- first query (A): SELECT id FROM events ORDER BY events.id DESC LIMIT 1 - I'm saving the result as a "max ID" for the second query

Just a suggestion. The first query is not really needed.

You can simply do:

second query (B):

SELECT id
, ...
  FROM events
 WHERE id > MIN(ID)
   AND id <= MAX(ID)
   AND ...

See https://www.postgresql.org/docs/9.6/static/functions-aggregate.html 

MAX and MIN functions

Hi Melvin, thank you for the suggestion. Unfortunately it won't do in my case. Sorry for not providing enough context in the first place.

After making the first query (A), I'm iterating over that table (with LIMIT 100 and increasing OFFSET) - using a query "B" - until another condition is met; overall, code is supposed to gather a number of rows from the table. I'm also using the "max ID" for another purpose. 

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.