Horton

Re: [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Peter Geoghegan <pg@bowt.ie>
Cc: "Wong, Yi Wen" <yiwong@amazon.com>, "Wood, Dan" <hexpert@amazon.com>, Michael Paquier <michael.paquier@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Robert Haas <robertmhaas@gmail.com>
Date: 2017-10-07T08:31:06Z
Lists: pgsql-hackers

Attachments

Peter Geoghegan wrote:
> On Fri, Oct 6, 2017 at 2:09 PM, Wong, Yi Wen <yiwong@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

Commits

  1. Backport addition of rs_old_rel to rewriteheap's state.

  2. Perform a lot more sanity checks when freezing tuples.

  3. Revert bogus fixes of HOT-freezing bug

  4. Fix traversal of half-frozen update chains

  5. Fix freezing of a dead HOT-updated tuple

  6. During index build, check and elog (not just Assert) for broken HOT chain.

  7. Fix WAL replay of locking an updated tuple

  8. Change the way we mark tuples as frozen.