Re: ANALYZE: ERROR: tuple already updated by self

Hi,

On 2019-06-18 18:12:33 -0500, Justin Pryzby wrote:
> A customers DB crashed due to OOM. While investigating the issue in our
> report, I created MV stats, which causes this error:
>
> ts=# CREATE STATISTICS sectors_stats (dependencies) ON site_id,sect_id FROM sectors;
> CREATE STATISTICS
> ts=# ANALYZE sectors;
> ERROR: XX000: tuple already updated by self
> LOCATION: simple_heap_update, heapam.c:4613
>
> The issue goes away if I drop the stats object and comes back if I recreate it.
>
> We're running 11.3 ; most of the (very few) reports from this error are from
> almost 10+ years ago, running pg7.3 like.
>
> I've taken a couple steps to resolve the issue (vacuum full and then reindex
> pg_statistic and its toast and the target table, which doesn't have a toast).
>
> I'm guessing the issue is with pg_statistic_ext, which I haven't touched.
>
> Next step seems to be to truncate pg_statistic{,ext} and re-analyze the DB.
>
> Does anyone want debugging/diagnostic info before I do that ?

Any chance to get a backtrace for the error?

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

You should be able to set a breakpoint to just the location pointed out
in the error message.

Greetings,

Andres Freund