1-- 2-- Verify that index scans encountering dead rows produced by an 3-- aborted subtransaction of the current transaction can utilize the 4-- kill_prio_tuple optimization 5-- 6-- NB: The table size is currently *not* expected to stay the same, we 7-- don't have logic to trigger opportunistic pruning in cases like 8-- this. 9BEGIN; 10 11SET LOCAL enable_seqscan = false; 12SET LOCAL enable_indexonlyscan = false; 13SET LOCAL enable_bitmapscan = false; 14 15-- Can't easily use a unique index, since dead tuples can be found 16-- independent of the kill_prior_tuples optimization. 17CREATE TABLE clean_aborted_self(key int, data text); 18CREATE INDEX clean_aborted_self_key ON clean_aborted_self(key); 19INSERT INTO clean_aborted_self (key, data) VALUES (-1, 'just to allocate metapage'); 20 21-- save index size from before the changes, for comparison 22SELECT pg_relation_size('clean_aborted_self_key') AS clean_aborted_self_key_before \gset 23 24DO $$ 25BEGIN 26 -- iterate often enough to see index growth even on larger-than-default page sizes 27 FOR i IN 1..100 LOOP 28 BEGIN 29 -- perform index scan over all the inserted keys to get them to be seen as dead 30 IF EXISTS(SELECT * FROM clean_aborted_self WHERE key > 0 AND key < 100) THEN 31 RAISE data_corrupted USING MESSAGE = 'these rows should not exist'; 32 END IF; 33 INSERT INTO clean_aborted_self SELECT g.i, 'rolling back in a sec' FROM generate_series(1, 100) g(i); 34 -- just some error that's not normally thrown 35 RAISE reading_sql_data_not_permitted USING MESSAGE = 'round and round again'; 36 EXCEPTION WHEN reading_sql_data_not_permitted THEN END; 37 END LOOP; 38END;$$; 39 40-- show sizes only if they differ 41SELECT :clean_aborted_self_key_before AS size_before, pg_relation_size('clean_aborted_self_key') size_after 42WHERE :clean_aborted_self_key_before != pg_relation_size('clean_aborted_self_key'); 43 44ROLLBACK; 45