Thread: 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.020160609, 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