2018-04-09 18:23 GMT-03:00 Peter Geoghegan <pg@bowt.ie>:
>
> On Mon, Apr 9, 2018 at 7:01 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
> > The bigger question is whether this can actually detect the issue. If
> > it's due to an storage issue, then perhaps yes. But if you only see
> > multixact issues consistently and nothing else, it might easily be a
> > PostgreSQL bug (in which case the checksum will be correct).
>
> You can also run amcheck. Get the version targeting earlier Postgres
> releases off Github (there are packages for most Linux systems). This
> can verify that the heap is consistent with indexes.
>
> --
> Peter Geoghegan
Hi Peter,
I ran amcheck in all index of a table and I only get empty returns.
I did some tests:
production=# reindex table fn06t;
REINDEX
production=# vacuum verbose fn06t;
INFO: vacuuming "public.fn06t"
ERROR: found multixact 76440919 from before relminmxid 122128619
production=# select count(*),sum(fn06vrtitu) from fn06t;
count | sum
---------+-----------------
2592363 | 4833603148.1172
(1 row)
#createdb freshdb
#pg_dump -t fn06t production | psql freshdb
freshdb=# select count(*),sum(fn06vrtitu) from fn06t;
count | sum
---------+-----------------
2592363 | 4833603148.1172
(1 row)
psql -c "select * from fn06t order by <PKEY>" production >
multixact_error_fn06t.txt
psql -c "select * from fn06t order by <PKEY>" freshdb > freshdb_fn06t.txt
diff points no differences in files and md5sum produces the same hash.
The question is: if a dump can read the table "correctly", why a
CLUSTER/vacuum full stops while reading them?
Best regards,
Alexandre