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
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 |