Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1eqS5C-0004K7-J5
	for pgsql-admin@arkaria.postgresql.org; Mon, 26 Feb 2018 23:21:22 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1eqS5A-0001Yy-TC
	for pgsql-admin@arkaria.postgresql.org; Mon, 26 Feb 2018 23:21:20 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <andres@anarazel.de>)
	id 1eqS5A-0001Yi-5l
	for pgsql-admin@lists.postgresql.org; Mon, 26 Feb 2018 23:21:20 +0000
Received: from out2-smtp.messagingengine.com ([66.111.4.26])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <andres@anarazel.de>)
	id 1eqS56-0002o9-Ro
	for pgsql-admin@postgresql.org; Mon, 26 Feb 2018 23:21:18 +0000
Received: from compute5.internal (compute5.nyi.internal [10.202.2.45])
	by mailout.nyi.internal (Postfix) with ESMTP id 0564720E5B;
	Mon, 26 Feb 2018 18:21:15 -0500 (EST)
Received: from frontend2 ([10.202.2.161])
  by compute5.internal (MEProxy); Mon, 26 Feb 2018 18:21:15 -0500
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=
	messagingengine.com; h=cc:content-type:date:from:in-reply-to
	:message-id:mime-version:references:subject:to:x-me-sender
	:x-me-sender:x-sasl-enc; s=fm2; bh=25+wwbFNT9K1k67XUPUV6Y16dC0g6
	0PY15bwya0RU2I=; b=SM4x+cFWIqUTlJfR+Q1tD3z/8hlpZCwzzbL7AD/HCRNRY
	flejR/N7+N25U9VySTTbMtU12cqGK465KKwNLGjxqKWhp6wi7l3wVWPfnPv9MmuG
	3iX7CoKbiUX0MPIZC2xVjGvHyuqQ1TnZ6joJmbLz5W+PhhbO4n5+7oAzyzVYUP4S
	s3/GAXz7r/ggLPFvPK0Mk3O6Z80ZebeXRhluHwbmRm78NUaEj56lAACCUAf3OqdX
	kiTmyHUblFBmDG3rbxykGkTCMxDxRxuqYqyD6FgYZC2UrjT+WYipq066nYcDDWRj
	c9AsvaNr4DIZrEbceLUjfLVRa9jP9eAsKM6EFmPIQ==
X-ME-Sender: <xms:apaUWqRFnJ8TS1fSMmB3cBIxEQOv2fNNs4jytKOj8PLQam4RC1TQog>
Received: from intern.anarazel.de (unknown [136.24.76.189])
	by mail.messagingengine.com (Postfix) with ESMTPA id B75B0246C8;
	Mon, 26 Feb 2018 18:21:14 -0500 (EST)
Date: Mon, 26 Feb 2018 15:21:13 -0800
From: Andres Freund <andres@anarazel.de>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Alexandre Garcia <alexandre@vmfarms.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: postgresql 9.6 - cannot freeze committed xmax
Message-ID: <20180226232113.a3aqlzpckcue4drt@alap3.anarazel.de>
References: 
 <CAPYLKR6yxV4=pfW0Gwij7aPNiiPx+3ib4USVYnbuQdUtmkMaEA@mail.gmail.com>
 <20180226230931.3m7js5dkzxnu7tbi@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20180226230931.3m7js5dkzxnu7tbi@alvherre.pgsql>
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk

On 2018-02-26 20:09:31 -0300, Alvaro Herrera wrote:
> Alexandre Garcia wrote:
> 
> > Recently our team performed an upgrade on one of our old postgres 9.2
> > databases. "for science" we tried a direct upgrade from 9.2 to 9.6  on our
> > staging environment . The initial plan was to do 9.2 -> 9.4 and 9.4 -> 9.6.
> > 
> > The upgrade turned out successful on staging and we decided to go with it
> > on prod as well.
> > Prod starting throwing the following errors during autovacuum -> 'cannot
> > freeze committed xmax <xid>' on 2 different tables. Running vacuum manually
> > revealed more tables affected by the same error.
> > 
> > The staging database did not present any error but the process to sync prod
> > to stage includes a sanitize script that removes sensitive information and
> > it somehow seems to fix the issue on stage (we've done a sync from prod to
> > stage after the upgrade)
> >
> > I've been doing a lot of search about this and even tried to go through the
> > code that throws that specific error.

Could you show pg_controldata from before/after pg_upgrade, and the
output of
SELECT oid::regclass, relfrozenxid, age(relfrozenxid), txid_current()
FROM pg_class
WHERE oid = 'problematic_table'::regclass;
from both before/after?


> This particular error condition is a sanity check that was only
> introduced in 9.6.7, so you would not find too many reports of that
> (this exact error message wording doesn't exist prior to that).  It is
> possible that we missed some corner case when writing that check.
> Upgrades from 9.2 are particularly unusual since the xmax header was
> reused in the 9.3 era to mean something completely different under some
> circumstances.  I'm not in a position to do deeper debugging for you at
> this time, though.
> 
> The commit in question is
> https://git.postgresql.org/pg/commitdiff/986a9153b9708071adf6ce2c9131266f3431f4ec
> 
> Wild guess: maybe we should be checking HEAP_LOCKED_UPGRADED before
> bailing out.

Doesn't !(tuple->t_infomask & HEAP_XMAX_LOCK_ONLY) already guard against
that?

Greetings,

Andres Freund