-- Every backend that reads a sift_table keeps the tombstone log of the table in memory, sorted (64 bytes an entry with the scratch of a first look), so the log is
-- bound by what one backend keeps (sift.tomb_log_limit, 8 GB): a read past it fails naming the setting and VACUUM, which is not bound and drops the entries of the
-- rows it cleared, and so does a statement whose own rows pass it. The test lowers the setting to 16 kB, 256 entries.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
CREATE TABLE tl (id int, v int) USING sift_table;
INSERT INTO tl SELECT i, i % 7 FROM generate_series(1, 4000) i;
VACUUM tl;
DELETE FROM tl WHERE id % 4 = 0;
CREATE FUNCTION tl_count() RETURNS text LANGUAGE plpgsql AS $$
DECLARE
	n bigint;
	hint text;
BEGIN
	SELECT count(*) INTO n FROM tl;
	RETURN 'ok ' || n;
EXCEPTION WHEN program_limit_exceeded THEN
	GET STACKED DIAGNOSTICS hint = PG_EXCEPTION_HINT;
	RETURN regexp_replace(SQLERRM, '[0-9]+', 'N', 'g') || ' / ' || hint;
END $$;
SELECT tl_count() AS default_limit;
 default_limit 
---------------
 ok 3000
(1 row)

-- a new backend reads the log from its start
\c -
SET sift.tomb_log_limit = '16kB';
SELECT tl_count() AS lowered;
                                                                                                                    lowered                                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 the tombstone log of relation "tl" holds N entries, more than a backend keeps of it (sift.tomb_log_limit = N kB) / Raise sift.tomb_log_limit (64 bytes of memory for each entry), or run VACUUM: it drops the entries of the rows it cleared.
(1 row)

-- VACUUM consumes the log, whatever its length: the table reads again
VACUUM tl;
SELECT tl_count() AS after_vacuum;
 after_vacuum 
--------------
 ok 3000
(1 row)

-- the entries of a statement's own rows count: a DELETE of more rows than the limit holds fails naming the setting, and runs with it raised
CREATE TABLE tm (id int) USING sift_table;
INSERT INTO tm SELECT i FROM generate_series(1, 2000) i;
CREATE FUNCTION tm_delete() RETURNS text LANGUAGE plpgsql AS $$
BEGIN
	DELETE FROM tm;
	RETURN 'deleted';
EXCEPTION WHEN program_limit_exceeded THEN
	RETURN regexp_replace(SQLERRM, '[0-9]+', 'N', 'g');
END $$;
\c -
SET sift.tomb_log_limit = '16kB';
SELECT tm_delete() AS own_rows_lowered;
                                                 own_rows_lowered                                                 
------------------------------------------------------------------------------------------------------------------
 the tombstone log of relation "tm" holds N entries, more than a backend keeps of it (sift.tomb_log_limit = N kB)
(1 row)

SET sift.tomb_log_limit = '1MB';
SELECT tm_delete() AS own_rows_raised;
 own_rows_raised 
-----------------
 deleted
(1 row)

SELECT count(*) AS left_rows FROM tm;
 left_rows 
-----------
         0
(1 row)

DROP FUNCTION tm_delete();
DROP TABLE tm;
DROP FUNCTION tl_count();
DROP TABLE tl;
DROP EXTENSION pg_sift;