-- BM25 `sift` index on a sift_table: build, maintenance, MVCC checks of candidates (delta rows have no visibility map),
-- agreement with the sequential scan, VACUUM, REINDEX CONCURRENTLY
CREATE EXTENSION pg_sift;
CREATE TABLE docs (id int, body text) USING sift_table;
INSERT INTO docs SELECT g, 'order number ' || g || CASE WHEN g % 2 = 0 THEN ' express shipping' ELSE ' standard shipping' END
FROM generate_series(1, 200) g;
CREATE INDEX docs_bm25 ON docs USING sift (body);
CREATE FUNCTION idx_ids(q text) RETURNS text LANGUAGE plpgsql SET enable_seqscan = off AS $$
DECLARE r text;
BEGIN
	EXECUTE format('SELECT count(*) || '':'' || coalesce(sum(id), 0) FROM docs WHERE body @@> %L', q) INTO r;
	RETURN r;
END $$;
CREATE FUNCTION seq_ids(q text) RETURNS text LANGUAGE plpgsql
	SET enable_indexscan = off SET enable_bitmapscan = off SET sift.enable_scan = off SET sift.enable_count = off AS $$
DECLARE r text;
BEGIN
	EXECUTE format('SELECT count(*) || '':'' || coalesce(sum(id), 0) FROM docs WHERE body @@> %L', q) INTO r;
	RETURN r;
END $$;
CREATE FUNCTION agree(q text) RETURNS text LANGUAGE sql
	RETURN idx_ids(q) || CASE WHEN idx_ids(q) = seq_ids(q) THEN '' ELSE ' MISMATCH ' || seq_ids(q) END;
SELECT agree('express'), agree('standard'), agree('number'), agree('absent');
   agree   |   agree   |   agree   | agree 
-----------+-----------+-----------+-------
 100:10100 | 100:10000 | 200:20100 | 0:0
(1 row)

-- maintenance: insert, update, delete
INSERT INTO docs VALUES (1000, 'express late arrival'), (1001, 'standard late arrival');
DELETE FROM docs WHERE id IN (2, 4, 5);
UPDATE docs SET body = 'refund processed' WHERE id IN (6, 7);
SELECT agree('express'), agree('standard'), agree('refund'), agree('late'), agree('arrival');
  agree   |  agree   | agree | agree  | agree  
----------+----------+-------+--------+--------
 98:11088 | 99:10989 | 2:13  | 2:2001 | 2:2001
(1 row)

-- rollback and savepoints leave no trace; own uncommitted rows are visible to the transaction itself
BEGIN;
INSERT INTO docs VALUES (2000, 'express phantom');
DELETE FROM docs WHERE id = 8;
SELECT agree('phantom'), agree('express');
 agree  |  agree   
--------+----------
 1:2000 | 98:13080
(1 row)

SAVEPOINT s;
UPDATE docs SET body = 'phantom again' WHERE id = 2000;
SELECT agree('phantom');
 agree  
--------
 1:2000
(1 row)

ROLLBACK TO s;
SELECT agree('phantom');
 agree  
--------
 1:2000
(1 row)

ROLLBACK;
SELECT agree('phantom'), agree('express');
 agree |  agree   
-------+----------
 0:0   | 98:11088
(1 row)

-- VACUUM, VACUUM FULL, REINDEX
VACUUM docs;
SELECT agree('express'), agree('refund');
  agree   | agree 
----------+-------
 98:11088 | 2:13
(1 row)

VACUUM (FULL) docs;
SELECT agree('express'), agree('refund');
  agree   | agree 
----------+-------
 98:11088 | 2:13
(1 row)

REINDEX INDEX docs_bm25;
SELECT agree('express'), agree('refund');
  agree   | agree 
----------+-------
 98:11088 | 2:13
(1 row)

REINDEX INDEX CONCURRENTLY docs_bm25;
SELECT agree('express'), agree('refund');
  agree   | agree 
----------+-------
 98:11088 | 2:13
(1 row)

-- ranked search through the index returns visible rows only
SET enable_seqscan = off;
SELECT id FROM docs WHERE body @@> 'late' ORDER BY id;
  id  
------
 1000
 1001
(2 rows)

RESET enable_seqscan;
-- CREATE INDEX CONCURRENTLY on a table with concurrent history
CREATE INDEX CONCURRENTLY docs_bm25_c ON docs USING sift (body);
SELECT agree('express');
  agree   
----------
 98:11088
(1 row)

DROP TABLE docs;
DROP EXTENSION pg_sift;
DROP FUNCTION agree(text), idx_ids(text), seq_ids(text);