Horton

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

Michael Paquier <michael.paquier@gmail.com>

View thread View on postgr.es
From: Michael Paquier <michael.paquier@gmail.com>
To: "Wood, Dan" <hexpert@amazon.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, "Wong, Yi Wen" <yiwong@amazon.com>, Peter Geoghegan <pg@bowt.ie>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2017-10-05T02:17:09Z
Lists: pgsql-hackers 
On Thu, Oct 5, 2017 at 10:39 AM, Wood, Dan <hexpert@amazon.com> wrote:
> Whatever you do make sure to also test 250 clients running lock.sql.  Even with the communities fix plus YiWen’s fix I can still get duplicate rows.  What works for “in-block” hot chains may not work when spanning blocks.

Interesting. Which version did you test? Only 9.6?

> Once nearly all 250 clients have done their updates and everybody is waiting to vacuum which one by one will take a while I usually just “pkill -9 psql”.  After that I have many of duplicate “id=3” rows.  On top of that I think we might have a lock leak.  After the pkill I tried to rerun setup.sql to drop/create the table and it hangs.  I see an autovacuum process starting and existing every couple of seconds.  Only by killing and restarting PG can I drop the table.

Yeah, that's more or less what I have been doing. My tests involve
using your initial script with way more sessions triggering lock.sql,
minus the kill-9 portion (good idea actually). I can of course see the
sessions queuing for VACUUM, still I cannot see duplicated rows, even
if I headshot Postgres in the middle of the VACUUM waiting queue. Note
that I have just tested Alvaro's patch on 9.3.
-- 
Michael

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.