No warning/error trying to vacuum other session's temp table

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: No warning/error trying to vacuum other session's temp table
Date: 2019-08-27 17:56:35
Message-ID: 87zhju7ar0.fsf@jsievers.enova.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(dot)consulting(at)comcast(dot)net

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Janes 2019-08-27 18:05:28 Re: Matching pgp_sym_encrypt() and gpg2 output
Previous Message Bruce Momjian 2019-08-27 17:33:16 Matching pgp_sym_encrypt() and gpg2 output