No warning/error trying to vacuum other session's temp table
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.0 20160609, 64-bit
(1 row)
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
Jerry Sievers <gsievers19@comcast.net> writes:
As seen below, the command response VACUUM is output , but adding the
VERBOSe keyword results in no additional output.
Yeah, per the code:
/*
* Silently ignore tables that are temp tables of other backends ---
* trying to vacuum these will lead to great unhappiness, since their
* contents are probably not up-to-date on disk. (We don't throw a
* warning here; it would just lead to chatter during a database-wide
* VACUUM.)
*/
if (RELATION_IS_OTHER_TEMP(onerel))
{
relation_close(onerel, lmode);
PopActiveSnapshot();
CommitTransactionCommand();
return false;
}
At the point where this is being done, we don't know whether the table was
explicitly named or not, so we really can't issue a message. Perhaps it'd
make sense to add an additional test earlier, where we do know that.
On the other hand, nobody has ever complained about this before.
regards, tom lane