gist-13-defer.sql

ALTER SYSTEM SET vacuum_defer_cleanup_age = 10000;
select pg_reload_conf();
select txid_current();

ROLLBACK PREPARED 'hold_horizon';
drop table if exists gist_point_tbl;

create table gist_point_tbl(p point, info text) with (autovacuum_enabled=false);
create index gist_pointidx on gist_point_tbl using gist(p);

-- create rows that will always appear dead
begin;
insert into gist_point_tbl (p, info)
    select point(g, 1000 + g), 'first'
    from generate_series(1, 10000) g;
rollback;

-- prevent truncation during vacuum, causes annoying waits
insert into gist_point_tbl (p, info)
    select point(g, 1000 + g), 'placeholder'
    from generate_series(10000, 10001) g;

-- hold back xid horizon
BEGIN;
SELECT txid_current();
PREPARE TRANSACTION 'hold_horizon';

-- first vacuum will never recycle
VACUUM VERBOSE gist_point_tbl;
-- second vacuum will erroneously recycle with vacuum_defer_cleanup_age > txid_current()
VACUUM VERBOSE gist_point_tbl;

-- I'm a nice person
ROLLBACK PREPARED 'hold_horizon';
ALTER SYSTEM RESET vacuum_defer_cleanup_age;
SELECT pg_reload_conf();