-- The write buffer lanes of the column engine (src/col/wbuf/wbuf.c): row records appended to the lanes of the active segment, sealing, the
-- reader, the sorted per-superblock batches, VACUUM's flags, torn rows, verification. The index is a sift_col index on an empty table
-- (a valid metapage and directory for the store layer); the test functions give it the schema hash of an orders table with line items.
-- Every row is checked in C against a generator, down to the bits of its floats and the bytes of its strings.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
CREATE FUNCTION expect(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE
	d text;
	h text;
BEGIN
	EXECUTE q;
	RETURN 'ok';
EXCEPTION WHEN OTHERS THEN
	GET STACKED DIAGNOSTICS d = PG_EXCEPTION_DETAIL, h = PG_EXCEPTION_HINT;
	RETURN regexp_replace(SQLSTATE || ': ' || SQLERRM || coalesce(' | ' || nullif(d, ''), '') || coalesce(' | ' || nullif(h, ''), ''), '[0-9]+', 'N', 'g');
END $$;
CREATE TABLE t (doc int);
CREATE INDEX t_idx ON t USING sift_col (doc) WITH (write_buffer_limit = 64);
NOTICE:  sift_col index "t_idx": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('t_idx');
SELECT count(*) AS segments FROM sift._test_col_wbuf_segments('t_idx');
 segments 
----------
        0
(1 row)

-- a definition that is not the index's is refused with the REINDEX hint
SELECT sift._test_col_wbuf_prepare('t_idx', 1) IS NOT NULL AS stale;
 stale 
-------
 t
(1 row)

SELECT expect($$ SELECT sift._test_col_wbuf_append('t_idx', 7, 0, 1) $$);
                                              expect                                              
--------------------------------------------------------------------------------------------------
 N: index "t_idx" was built with other column definitions | Use REINDEX to apply the new options.
(1 row)

SELECT sift._test_col_wbuf_prepare('t_idx') IS NOT NULL AS prepared;
 prepared 
----------
 t
(1 row)

-- 3000 rows from this backend: one active segment, one lane, the segment found full (every 1000th row is a record of many pages)
SELECT sift._test_col_wbuf_append('t_idx', 7, 0, 3000) > 0 AS found_full;
 found_full 
------------
 t
(1 row)

SELECT kind, nrows, live, lanes, npages > 16 AS over_limit, npages = extents AS extents_cover
FROM sift._test_col_wbuf_segments('t_idx');
  kind  | nrows | live | lanes | over_limit | extents_cover 
--------+-------+------+-------+------------+---------------
 active |  3000 | 3000 |     1 | t          | t
(1 row)

CREATE TABLE r AS SELECT * FROM sift._test_col_wbuf_rows('t_idx', 7);
SELECT count(*) AS records, count(DISTINCT id) AS ids, min(id), max(id), count(*) FILTER (WHERE deleted) AS deleted FROM r;
 records | ids  | min | max  | deleted 
---------+------+-----+------+---------
    3000 | 3000 |   0 | 2999 |       0
(1 row)

SELECT sift._test_check('t_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- batches: one per superblock in ascending order, keys ascending, the same rows
CREATE TABLE b AS SELECT * FROM sift._test_col_wbuf_batches('t_idx', 7);
SELECT count(*) AS rows, count(DISTINCT id) AS ids, count(DISTINCT sb) > 1 AS superblocks FROM b;
 rows | ids  | superblocks 
------+------+-------------
 3000 | 3000 | t
(1 row)

SELECT (SELECT count(*) FROM (SELECT id FROM r EXCEPT SELECT id FROM b) x) AS only_in_records,
	   (SELECT count(*) FROM (SELECT id FROM b EXCEPT SELECT id FROM r) x) AS only_in_batches;
 only_in_records | only_in_batches 
-----------------+-----------------
               0 |               0
(1 row)

-- sealing: above the limit only, then the sealed segment is read as before and a fresh one takes the appends
SELECT sift._test_col_wbuf_seal('t_idx') AS sealed;
 sealed 
--------
      1
(1 row)

SELECT sift._test_col_wbuf_seal('t_idx') AS sealed_again;
 sealed_again 
--------------
            0
(1 row)

SELECT sift._test_col_wbuf_seal('t_idx', true) AS forced_empty;
 forced_empty 
--------------
            0
(1 row)

SELECT kind, nrows, live FROM sift._test_col_wbuf_segments('t_idx') ORDER BY gen;
  kind  | nrows | live 
--------+-------+------
 sealed |  3000 | 3000
 active |     0 |    0
(2 rows)

SELECT count(*) AS records, count(DISTINCT id) AS ids FROM sift._test_col_wbuf_rows('t_idx', 7);
 records | ids  
---------+------
    3000 | 3000
(1 row)

-- appends that find the lane tails locked go to other lanes
SELECT sift._test_col_wbuf_append('t_idx', 7, 3000, 4000, true) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT kind, nrows, live, lanes > 1 AS several_lanes FROM sift._test_col_wbuf_segments('t_idx') ORDER BY gen;
  kind  | nrows | live | several_lanes 
--------+-------+------+---------------
 sealed |  3000 | 3000 | f
 active |  4000 | 4000 | t
(2 rows)

SELECT count(*) AS records, count(DISTINCT id) AS ids FROM sift._test_col_wbuf_rows('t_idx', 7);
 records | ids  
---------+------
    7000 | 7000
(1 row)

SELECT sift._test_check('t_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- a big row spans pages and is read back whole
CREATE TABLE p AS SELECT npages FROM sift._test_col_wbuf_segments('t_idx') WHERE kind = 'active';
SELECT sift._test_col_wbuf_append('t_idx', 7, 8999, 1) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT (SELECT npages FROM sift._test_col_wbuf_segments('t_idx') WHERE kind = 'active') - (SELECT npages FROM p) >= 3 AS several_pages;
 several_pages 
---------------
 t
(1 row)

SELECT count(*) AS found FROM sift._test_col_wbuf_rows('t_idx', 7) WHERE id = 8999;
 found 
-------
     1
(1 row)

-- a row whose writer died after some of its pieces is skipped, and the lane goes on
SELECT expect($$ SELECT sift._test_col_wbuf_torn('t_idx', 7, 9999, 1) $$);
                             expect                             
----------------------------------------------------------------
 XXN: sift_col write buffer: injected failure after a page link
(1 row)

SELECT expect($$ SELECT sift._test_col_wbuf_torn('t_idx', 7, 10999, 3) $$);
                             expect                             
----------------------------------------------------------------
 XXN: sift_col write buffer: injected failure after a page link
(1 row)

SELECT sift._test_col_wbuf_append('t_idx', 7, 11000, 50) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT count(*) AS torn_seen FROM sift._test_col_wbuf_rows('t_idx', 7) WHERE id IN (9999, 10999);
 torn_seen 
-----------
         0
(1 row)

SELECT count(*) AS torn_batched FROM sift._test_col_wbuf_batches('t_idx', 7) WHERE id IN (9999, 10999);
 torn_batched 
--------------
            0
(1 row)

SELECT count(*) AS records, count(DISTINCT id) AS ids FROM sift._test_col_wbuf_rows('t_idx', 7) WHERE id >= 11000;
 records | ids 
---------+-----
      50 |  50
(1 row)

SELECT kind, live FROM sift._test_col_wbuf_segments('t_idx') ORDER BY gen;
  kind  | live 
--------+------
 sealed | 3000
 active | 4051
(2 rows)

SELECT sift._test_check('t_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- a repeated TID keeps its first record in the batches
SELECT sift._test_col_wbuf_append('t_idx', 7, 12000, 1) + sift._test_col_wbuf_append('t_idx', 7, 12000, 1) >= 0 AS twice;
 twice 
-------
 t
(1 row)

SELECT (SELECT count(*) FROM sift._test_col_wbuf_rows('t_idx', 7) WHERE id = 12000) AS records,
	   (SELECT count(*) FROM sift._test_col_wbuf_batches('t_idx', 7) WHERE id = 12000) AS batched;
 records | batched 
---------+---------
       2 |       1
(1 row)

-- First fragments of torn rows depend on the randomized lane tail room. VACUUM must preserve their physical count.
CREATE TABLE counts AS SELECT gen, nrows FROM sift._test_col_wbuf_segments('t_idx');
-- VACUUM: the callback flags the dead rows once, in the sealed and the active segment; batches leave them out
SELECT sift._test_col_wbuf_delete('t_idx', 5) AS flagged;
 flagged 
---------
    1412
(1 row)

SELECT sift._test_col_wbuf_delete('t_idx', 5) AS flagged_again;
 flagged_again 
---------------
             0
(1 row)

SELECT count(*) AS records, count(*) FILTER (WHERE deleted) AS deleted, count(*) FILTER (WHERE deleted AND id % 5 <> 0) AS wrongly_deleted
FROM sift._test_col_wbuf_rows('t_idx', 7);
 records | deleted | wrongly_deleted 
---------+---------+-----------------
    7053 |    1412 |               0
(1 row)

SELECT count(*) AS batched, count(*) FILTER (WHERE id % 5 = 0) AS dead_batched FROM sift._test_col_wbuf_batches('t_idx', 7);
 batched | dead_batched 
---------+--------------
    5641 |            0
(1 row)

SELECT s.kind, s.nrows = c.nrows AS same_nrows, s.live
FROM sift._test_col_wbuf_segments('t_idx') s JOIN counts c USING (gen) ORDER BY gen;
  kind  | same_nrows | live 
--------+------------+------
 sealed | t          | 2400
 active | t          | 3241
(2 rows)

SELECT sift._test_check('t_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- the batches a scan keeps are the buffer as it is now: a read after VACUUM flagged records leaves them out, rows appended since are in
CREATE TABLE t4 (doc int);
CREATE INDEX t4_idx ON t4 USING sift_col (doc) WITH (write_buffer_limit = 64);
NOTICE:  sift_col index "t4_idx": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('t4_idx');
SELECT sift._test_col_wbuf_prepare('t4_idx') IS NOT NULL AS prepared;
 prepared 
----------
 t
(1 row)

SELECT sift._test_col_wbuf_append('t4_idx', 7, 0, 600) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT count(*) AS kept, count(*) FILTER (WHERE id % 5 = 0) AS dead_kept FROM sift._test_col_wbuf_batches('t4_idx', 7, true);
 kept | dead_kept 
------+-----------
  600 |       120
(1 row)

SELECT count(*) AS kept_again FROM sift._test_col_wbuf_batches('t4_idx', 7, true);
 kept_again 
------------
        600
(1 row)

SELECT sift._test_col_wbuf_delete('t4_idx', 5) AS flagged;
 flagged 
---------
     120
(1 row)

SELECT count(*) AS kept, count(*) FILTER (WHERE id % 5 = 0) AS dead_kept FROM sift._test_col_wbuf_batches('t4_idx', 7, true);
 kept | dead_kept 
------+-----------
  480 |         0
(1 row)

SELECT sift._test_col_wbuf_append('t4_idx', 7, 600, 100) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT sift._test_col_wbuf_delete('t4_idx', 7) AS flagged_more;
 flagged_more 
--------------
           82
(1 row)

SELECT sift._test_col_wbuf_append('t4_idx', 7, 700, 100) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT (SELECT count(*) FROM (SELECT id FROM sift._test_col_wbuf_batches('t4_idx', 7, true) EXCEPT SELECT id FROM sift._test_col_wbuf_batches('t4_idx', 7)) x) AS only_kept,
	   (SELECT count(*) FROM (SELECT id FROM sift._test_col_wbuf_batches('t4_idx', 7) EXCEPT SELECT id FROM sift._test_col_wbuf_batches('t4_idx', 7, true)) x) AS only_fresh,
	   (SELECT count(*) FROM sift._test_col_wbuf_batches('t4_idx', 7, true)) AS kept;
 only_kept | only_fresh | kept 
-----------+------------+------
         0 |          0 |  598
(1 row)

SELECT sift._test_check('t4_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- a scan keeps the version it read at open: a VACUUM between two of its reads leaves the second on the batches of the first (the rows VACUUM flagged are
-- dead to its snapshot, which held VACUUM back from anything it sees), and a scan that opens afterwards reads the flags. Only id 0 is flagged here, on the
-- first page of its lane
CREATE TABLE t5 (doc int);
CREATE INDEX t5_idx ON t5 USING sift_col (doc) WITH (write_buffer_limit = 64);
NOTICE:  sift_col index "t5_idx": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('t5_idx');
SELECT sift._test_col_wbuf_prepare('t5_idx') IS NOT NULL AS prepared;
 prepared 
----------
 t
(1 row)

SELECT sift._test_col_wbuf_append('t5_idx', 7, 0, 3000) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT (SELECT max(npages) FROM sift._test_col_wbuf_segments('t5_idx')) > 8 AS many_pages;
 many_pages 
------------
 t
(1 row)

CREATE TABLE opened AS SELECT sift._test_col_dir_version('t5_idx') AS v;
SELECT count(*) AS first_read FROM sift._test_col_wbuf_batches('t5_idx', 7, true, (SELECT v FROM opened));
 first_read 
------------
       3000
(1 row)

SELECT sift._test_col_wbuf_delete('t5_idx', 100000) AS flagged;
 flagged 
---------
       1
(1 row)

SELECT sift._test_col_dir_version('t5_idx') > (SELECT v FROM opened) AS bumped;
 bumped 
--------
 t
(1 row)

SELECT count(*) AS second_read, count(*) FILTER (WHERE id = 0) AS flagged_kept FROM sift._test_col_wbuf_batches('t5_idx', 7, true, (SELECT v FROM opened));
 second_read | flagged_kept 
-------------+--------------
        3000 |            1
(1 row)

SELECT count(*) AS later_scan, count(*) FILTER (WHERE id = 0) AS flagged_kept FROM sift._test_col_wbuf_batches('t5_idx', 7, true);
 later_scan | flagged_kept 
------------+--------------
       2999 |            0
(1 row)

-- ... except in the page it goes on reading (the last of each lane): flags set there since the last read are found whatever version it holds, or the next scan
-- would take the stamp of that read for a check
CREATE TABLE t6 (doc int);
CREATE INDEX t6_idx ON t6 USING sift_col (doc) WITH (write_buffer_limit = 64);
NOTICE:  sift_col index "t6_idx": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('t6_idx');
SELECT sift._test_col_wbuf_prepare('t6_idx') IS NOT NULL AS prepared;
 prepared 
----------
 t
(1 row)

SELECT sift._test_col_wbuf_append('t6_idx', 7, 0, 600) >= 0 AS appended;
 appended 
----------
 t
(1 row)

CREATE TABLE opened6 AS SELECT sift._test_col_dir_version('t6_idx') AS v;
SELECT count(*) AS first_read FROM sift._test_col_wbuf_batches('t6_idx', 7, true, (SELECT v FROM opened6));
 first_read 
------------
        600
(1 row)

SELECT sift._test_col_wbuf_delete('t6_idx', 5) AS flagged;
 flagged 
---------
     120
(1 row)

SELECT count(*) AS resumed_read, count(*) FILTER (WHERE id % 5 = 0 AND id < 600) AS flagged_kept FROM sift._test_col_wbuf_batches('t6_idx', 7, true, (SELECT v FROM opened6));
 resumed_read | flagged_kept 
--------------+--------------
          480 |            0
(1 row)

SELECT count(*) AS later_scan, count(*) FILTER (WHERE id % 5 = 0 AND id < 600) AS flagged_kept FROM sift._test_col_wbuf_batches('t6_idx', 7, true);
 later_scan | flagged_kept 
------------+--------------
        480 |            0
(1 row)

-- a string of any length is held: a record of 300 000 bytes spans pages
SELECT sift._test_col_wbuf_long('t_idx');
 _test_col_wbuf_long 
---------------------
 
(1 row)

SELECT sift._test_check('t_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- A full tail cannot hold even the first fragment. Failing after its page link leaves no new record;
-- failing again on the fresh empty tail leaves one first fragment, but neither interruption leaves a complete row.
CREATE TABLE t3 (doc int);
CREATE INDEX t3_idx ON t3 USING sift_col (doc) WITH (write_buffer_limit = 64);
NOTICE:  sift_col index "t3_idx": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('t3_idx');
SELECT sift._test_col_wbuf_prepare('t3_idx') IS NOT NULL AS prepared;
 prepared 
----------
 t
(1 row)

-- Seed 7, ids 0..160 fill the lane tail exactly, without randomizing busy lanes.
SELECT sift._test_col_wbuf_append('t3_idx', 7, 0, 161) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT pg.upper = pg.lower AS tail_full
FROM generate_series(0, pg_relation_size('t3_idx') / 8192 - 1) b, LATERAL sift._test_page('t3_idx', b) pg
WHERE pg.type = 7 AND pg.lower > 32 ORDER BY b DESC LIMIT 1;
 tail_full 
-----------
 t
(1 row)

SELECT expect($$ SELECT sift._test_col_wbuf_torn('t3_idx', 7, 9999, 1) $$);
                             expect                             
----------------------------------------------------------------
 XXN: sift_col write buffer: injected failure after a page link
(1 row)

SELECT nrows, live FROM sift._test_col_wbuf_segments('t3_idx');
 nrows | live 
-------+------
   161 |  161
(1 row)

SELECT expect($$ SELECT sift._test_col_wbuf_torn('t3_idx', 7, 10999, 1) $$);
                             expect                             
----------------------------------------------------------------
 XXN: sift_col write buffer: injected failure after a page link
(1 row)

SELECT nrows, live FROM sift._test_col_wbuf_segments('t3_idx');
 nrows | live 
-------+------
   162 |  161
(1 row)

SELECT sift._test_col_wbuf_append('t3_idx', 7, 11000, 1) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT nrows, live FROM sift._test_col_wbuf_segments('t3_idx');
 nrows | live 
-------+------
   163 |  162
(1 row)

SELECT count(*) AS records, count(*) FILTER (WHERE id IN (9999, 10999)) AS torn_seen
FROM sift._test_col_wbuf_rows('t3_idx', 7);
 records | torn_seen 
---------+-----------
     162 |         0
(1 row)

SELECT count(*) AS batched, count(*) FILTER (WHERE id IN (9999, 10999)) AS torn_batched
FROM sift._test_col_wbuf_batches('t3_idx', 7);
 batched | torn_batched 
---------+--------------
     162 |            0
(1 row)

SELECT sift._test_check('t3_idx') AS orphans;
 orphans 
---------
       0
(1 row)

-- corruption is found: a page header count, a flag byte, the length of a record
CREATE TABLE t2 (doc text);
CREATE INDEX t2_idx ON t2 USING sift_col (doc) WITH (write_buffer_limit = 64);
NOTICE:  sift_col index "t2_idx": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('t2_idx');
SELECT sift._test_col_wbuf_prepare('t2_idx') IS NOT NULL AS prepared;
 prepared 
----------
 t
(1 row)

SELECT sift._test_col_wbuf_append('t2_idx', 3, 0, 20) >= 0 AS appended;
 appended 
----------
 t
(1 row)

SELECT live FROM sift._test_col_wbuf_segments('t2_idx');
 live 
------
   20
(1 row)

CREATE TABLE pages AS SELECT b AS blk, pg.type, pg.lower FROM generate_series(0, pg_relation_size('t2_idx') / 8192 - 1) b, LATERAL sift._test_page('t2_idx', b) pg;
-- offsets: the data starts at 24, the page head is nrecs at +4, flags at +6, the first record's head follows at +8 (flags at +6, len at +8)
SELECT min(blk) AS empty_page FROM pages WHERE type = 7 AND lower = 32 \gset
SELECT min(blk) AS rec_page FROM pages WHERE type = 7 AND lower > 32 \gset
SELECT sift._test_poke('t2_idx', :empty_page, 28, 5, 2);
 _test_poke 
------------
 
(1 row)

SELECT expect($$ SELECT * FROM sift._test_col_wbuf_segments('t2_idx') $$);
                                          expect                                          
------------------------------------------------------------------------------------------
 XXN: sift index "tN_idx" is corrupted | Write buffer block N holds N rows, its header N.
(1 row)

SELECT sift._test_poke('t2_idx', :empty_page, 28, 0, 2);
 _test_poke 
------------
 
(1 row)

SELECT sift._test_poke('t2_idx', :rec_page, 38, 16, 2);
 _test_poke 
------------
 
(1 row)

SELECT expect($$ SELECT * FROM sift._test_col_wbuf_segments('t2_idx') $$);
                                          expect                                          
------------------------------------------------------------------------------------------
 XXN: sift index "tN_idx" is corrupted | A write buffer record has tid (N,N) and flags N.
(1 row)

SELECT sift._test_poke('t2_idx', :rec_page, 38, 0, 2);
 _test_poke 
------------
 
(1 row)

SELECT sift._test_poke('t2_idx', :rec_page, 40, 100000, 4);
 _test_poke 
------------
 
(1 row)

SELECT expect($$ SELECT * FROM sift._test_col_wbuf_segments('t2_idx') $$);
                                                         expect                                                          
-------------------------------------------------------------------------------------------------------------------------
 XXN: sift index "tN_idx" is corrupted | A record of N bytes at byte N overruns the N used bytes of a write buffer page.
(1 row)

SELECT expect($$ SELECT * FROM sift._test_col_wbuf_rows('t2_idx', 3) $$);
                                                         expect                                                          
-------------------------------------------------------------------------------------------------------------------------
 XXN: sift index "tN_idx" is corrupted | A record of N bytes at byte N overruns the N used bytes of a write buffer page.
(1 row)

DROP TABLE t, t2, t3, t4, t5, t6, opened, opened6, r, b, p, pages, counts;
DROP FUNCTION expect(text);
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;