Peter Geoghegan wrote: > On Fri, Oct 6, 2017 at 2:09 PM, Wong, Yi Wen <yiw...@amazon.com> wrote: > > Yesterday, I've been spending time with pg_visibility on the pages when I > > reproduce the issue in 9.6. > > None of the all-frozen or all-visible bits are necessarily set in > > problematic pages. > > Since this happened yesterday, I assume it was with an unfixed version? > > As you must have seen, Alvaro said he has a variant of Dan's original > script that demonstrates that a problem remains, at least on 9.6+, > even with today's fix. I think it's the stress-test that plays with > fillfactor, many clients, etc [1].
I just execute setup.sql once and then run this shell command, while :; do psql -e -P pager=off -f ./repro.sql for i in `seq 1 5`; do psql -P pager=off -e --no-psqlrc -f ./lock.sql & done wait && psql -P pager=off -e --no-psqlrc -f ./reindex.sql psql -P pager=off -e --no-psqlrc -f ./report.sql echo "done" done Note that you need to use pg10's psql because of the \if lines in lock.sql. For some runs I change the values to compare random() to, and originally the commented out section in lock.sql was not commented out, but I'm fairly sure the failures I saw where with this version. Also, I sometime change the 5 in the `seq` command to higher values (180, 250). I didn't find the filler column to have any effect, so I took that out. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
begin; values ('for key share') /*, ('') */ order by random() limit 1 \gset select pg_sleep(random() * 0.03); select id from t where id=3 :column1 ; select random() > .0 as update \gset \if :update select pg_sleep(random() * 0.03); update t set x=x+1 where id=3; \endif /* select random() > .0 as update2 \gset \if :update2 savepoint foo; update t set x=x+1 where id=3; \endif select random() > .5 as rollback_to \gset \if :rollback_to rollback to foo; \endif */ select random() > .0 as commit \gset \if :commit commit; \else rollback; \endif select pg_sleep(random() * 0.03); vacuum freeze t;
reindex index t_pkey;
with pages (blkno) as (select generate_series::int from generate_series(0, pg_relation_size('t')/current_setting('block_size')::int - 1)), rawpages (blkno, pg) as (select blkno, get_raw_page from pages, get_raw_page('t', blkno)), heapitems as (select blkno, heap_page_items.* from rawpages, heap_page_items(pg)) select blkno, lp, lp_flags, lp_off, t_xmin, t_xmax, t_ctid, infomask(t_infomask, 1) as infomask, infomask(t_infomask2, 2) as infomask2 from heapitems where lp_off <> 0
drop table t; create table t (id int primary key, name char(3), x integer) -- with (fillfactor = 10) ; insert into t values (1, '111', 0); insert into t values (3, '333', 0);
create type infomask_bit_desc as (mask varbit, symbol text); create or replace function infomask(msk int, which int) returns text language plpgsql as $$ declare r infomask_bit_desc; str text = ''; append_bar bool = false; begin for r in select * from infomask_bits(which) loop if (msk::bit(16) & r.mask)::int <> 0 then if append_bar then str = str || '|'; end if; append_bar = true; str = str || r.symbol; end if; end loop; return str; end; $$ ; create or replace function infomask_bits(which int) returns setof infomask_bit_desc language plpgsql as $$ begin if which = 1 then return query values (x'8000'::varbit, 'MOVED_IN'), (x'4000', 'MOVED_OFF'), (x'2000', 'UPDATED'), (x'1000', 'XMAX_IS_MULTI'), (x'0800', 'XMAX_INVALID'), (x'0400', 'XMAX_COMMITTED'), (x'0200', 'XMIN_INVALID'), (x'0100', 'XMIN_COMMITTED'), (x'0080', 'XMAX_LOCK_ONLY'), (x'0040', 'EXCL_LOCK'), (x'0020', 'COMBOCID'), (x'0010', 'XMAX_KEYSHR_LOCK'), (x'0008', 'HASOID'), (x'0004', 'HASEXTERNAL'), (x'0002', 'HASVARWIDTH'), (x'0001', 'HASNULL'); elsif which = 2 then return query values (x'2000'::varbit, 'UPDATE_KEY_REVOKED'), (x'4000', 'HOT_UPDATED'), (x'8000', 'HEAP_ONLY_TUPLE'); end if; end; $$; create extension if not exists pageinspect;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers