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