Re: Old row version in hot chain become visible after a freeze

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