Greetings!
Spent a bit of head scratching time today responding to a case of old
temp tables possibly threatening one of our reporting systems due to
txid wrap.
There is such an old enough backendID still live that I speculated one
or more of these temp tables still possibly in use... which got me to
dreaming that I had once or more previously vac'd such tables from
another session.
And then tried it.
As seen below, the command response VACUUM is output , but adding the
VERBOSe keyword results in no additional output.
Thus I believe this doesn't work, and never has??
An attempt to trunc such a table as shown in the test case results in a
clear error.
None of the conditions such as old xact/prepared-xact nor rogue slots
that would prevent vacuum from advancing a table's relfrozenxid were
found to exist.
Please advise. Thx
>select oid::regclass from pg_class where relname='foozzz';
oid
------------------
pg_temp_9.foozzz
(1 row)
-- not my temp table
>\gset
>vacuum verbose :oid;
VACUUM
>truncate :oid;
ERROR: cannot truncate temporary tables of other sessions
>drop table :oid;
DROP TABLE
-- my temp table
>create temp table foo (a int);
CREATE TABLE
>vacuum verbose foo;
INFO: vacuuming "pg_temp_19.foo"
INFO: "foo": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 5756854
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
>select version();
version
---------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11)
5.4.020160609, 64-bit
(1 row)
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net