PostgreSQL “tuple already updated by self”

On Tue, May 22, 2018 at 8:01 AM, SG <combatcode@gmail.com> wrote:

Our database seems to be broken, normally it uses about 1-2% of cpu, but if
we run some additional backend services making UPDATE and INSERT queries for
10M rows table (about 1 query per 3 second) everything is going to hell
(including CPU increase from 2% to 98% usage).

We have decided to debug what's going on, run VACUUM and ANALYZE to learn
what's wrong with db but...

production=# ANALYZE VERBOSE users_user;
INFO: analyzing "public.users_user"
INFO: "users_user": scanned 280 of 280 pages, containing 23889 live rows
and 57 dead rows; 23889 rows in sample, 23889 estimated total rows
INFO: analyzing "public.users_user"
INFO: "users_user": scanned 280 of 280 pages, containing 23889 live rows
and 57 dead rows; 23889 rows in sample, 23889 estimated total rows
ERROR: tuple already updated by self

we are not able to finish ANALYZE on ANY of the tables and could not find
any information about this issue. Any suggestions what can be wrong?

Well, as Laurenz Albe said on the Stack Overflow thread, it sure looks
like ANALYZE is finding the same table (public.users_user) twice,
which it shouldn't do. So something is probably wrong with pg_class.
He suggested a duplicate heap tuple, which could well be right, or
maybe it's a broken HOT chain or a bad index or something. If it were
me, I'd probably try what he suggested and then get busy with
pageinspect and/or pg_filedump if that didn't work.

Another interesting question is how the system got into this state in
the first place, of course. Was it a bug, or did you do something
that corrupted the database?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, May 22, 2018 at 09:17:15AM -0400, Robert Haas wrote:

Another interesting question is how the system got into this state in
the first place, of course. Was it a bug, or did you do something
that corrupted the database?

Perhaps a side effect which has been fixed in d2599ecf for 9.6.9?
The thing is that pruning chains broken by bugs like freeze-the-dead
could remain undetected for some time. On top of that autovacuum does
not check after duplicated OIDs registered when building the list, and
executes each table in its own transaction, so an autoanalyze would not
have seen the issue, still it was doing twice the work. One thing could
be to run diagnostic checks on all system catalogs (pg_catcheck can
help here).
--
Michael