Date: Thu, 28 Sep 2017 17:00:29 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jeff Frost <jeff@pgexperts.com>
Cc: Michael Paquier <michael.paquier@gmail.com>,
	Peter Geoghegan <pg@bowt.ie>, "Wood, Dan" <hexpert@amazon.com>,
	"pgsql-bugs@postgresql.org" <pgsql-bugs@postgresql.org>,
	Andres Freund <andres@anarazel.de>
Subject: Re: Old row version in hot chain become visible after a freeze
Message-ID: <20170928150029.4tqfrdfw5sh452af@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <15936472-A918-4353-B4BB-B59E0B573490@pgexperts.com>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

Jeff Frost wrote:

> Any idea on how to identify affected rows on a running system?

> Is it as simple as:
> 
> SELECT id, count(*) FROM foo GROUP BY id HAVING count(*) > 1;
> 
> Maybe also need to:
> 
> set enable_indexscan = 0;
> set enable_indexonlyscan = 0;
> 
> before running the SELECT?

Did you find out?  Your proposed query/settings seems like a good way to
determine if any supposed unique column is still unique, but I wonder if
there's a better way.  I suppose a procedure would involve seeing which
tables have HOT updates, and scan those.

Of course, this could theoretically happen to tables without any UNIQUE,
and then the recipe doesn't work.

> Is it possible to affect a DELETE or does it need to be a HOT updated row?

AFAICS it needs to be a HOT update.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs