0

I am using Postgres with psycopg2 and sometimes when I run VACUUM VERBOSE table_name; it gives me a line giving the oldest xmin and sometimes it does not. Is there a way to make sure that it always provides it?

Here is the output one that where it didn't give me the value:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.02u sec elapsed 0.03 sec.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.

Here is one time where it did give it to me:

INFO:  vacuuming "public.homeless_by_coc"

INFO:  scanned index "homeless_by_coc_pkey" to remove 86530 row versions
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s

INFO:  "homeless_by_coc": removed 86530 row versions in 1280 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

INFO:  index "homeless_by_coc_pkey" now contains 0 row versions in 239 pages
DETAIL:  86530 index row versions were removed.
236 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "homeless_by_coc": found 86530 removable, 0 nonremovable row versions in 1280 out of 1280 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s.

INFO:  "homeless_by_coc": truncated 1280 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

You will notice that the second time there is the following line:

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 16410
1

This does not appear to be a conditional. The extra information was added in v10 with commit 9eb344faf5, and is always printed in that version and up for a VACUUM VERBOSE (it apparently is not printed for a CLUSTER, which does seem undesirable).

If you want consistent behavior, you need to upgrade all your database instances to the same version of PostgreSQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.