From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jeff Frost <jeff@pgexperts.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Wood, Dan <hexpert@amazon.com>
Cc: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Subject: Re: Old row version in hot chain become visible after a freeze
Date: Thu, 28 Sep 2017 17:00:29 +0200
Message-ID: <20170928150029.4tqfrdfw5sh452af@alvherre.pgsql> (raw)
In-Reply-To: <15936472-A918-4353-B4BB-B59E0B573490@pgexperts.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@alvh.no-ip.org, jeff@pgexperts.com, michael.paquier@gmail.com, pg@bowt.ie, hexpert@amazon.com, andres@anarazel.de
Subject: Re: Old row version in hot chain become visible after a freeze
In-Reply-To: <20170928150029.4tqfrdfw5sh452af@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox