Subsequent writes become visible to cursor when reading columnar tables

[onurctirtir]

Originally posted by @onurctirtir in #5154 (comment)

Below scenario is also reproduceable on older versions (at least on v10.1.2).
The root cause is that we build the readState when reading the first row, so we don't use the snaphot provided when running DECLARE command.

Even if we've called init_columnar_read_state in begin_read, we would still encounter with this issue when doing index-scan. This is because, index_begin_read doesn't provide a snapshot. For this reason, we have to use the snapshot passed to index_fetch_tuple there, which first calls init_columnar_read_state and that flushes the pending writes of the current xact first ((5, 6), (7, 8)).

Unfortunately, #5154 wouldn't fix that issue.

CREATE TABLE columnar_table(q1 int8, q2 int8) USING columnar;
INSERT INTO columnar_table VALUES (1, 2), (3, 4);

BEGIN;
  DECLARE columnar_cursor CURSOR WITH HOLD FOR
  SELECT * FROM columnar_table ORDER BY 1,2;

  INSERT INTO columnar_table VALUES (5, 6), (7, 8);

  -- bad, shows (5, 6) and (7, 8) as well :/
  -- heapAM would only show (1, 2) & (3, 4)  
  FETCH ALL FROM columnar_cursor;
┌────┬────┐
│ q1 │ q2 │
├────┼────┤
│  1 │  2 │
│  3 │  4 │
│  5 │  6 │
│  7 │  8 │
└────┴────┘
(4 rows)