Thread: BUG #2239: "vacuumdb -a" remove freeze
The following bug has been logged online:
Bug reference: 2239
Logged by: Olleg Samoylov
Email address: olleg_s@mail.ru
PostgreSQL version: 8.1
Operating system: Linux (debian-amd64)
Description: "vacuumdb -a" remove freeze
Details:
template1=# select version();
version
----------------------------------------------------------------------------
------------------------------------
PostgreSQL 8.1.0 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3
20051111 (prerelease) (Debian 4.0.2-4)
(1 row)
template1=# select * from pg_database where datname='template1';
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit
| datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig |
datacl
-----------+--------+----------+---------------+--------------+-------------
-+---------------+--------------+--------------+---------------+-----------+
------------------------
template1 | 10 | 6 | t | t | -1
| 10792 | 1112 | 3221226585 | 1663 | |
{postgres=CT/postgres}
(1 row)
template1=# vacuum freeze;
VACUUM
template1=# select * from pg_database where datname='template1';
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit
| datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig |
datacl
-----------+--------+----------+---------------+--------------+-------------
-+---------------+--------------+--------------+---------------+-----------+
------------------------
template1 | 10 | 6 | t | t | -1
| 10792 | 1172 | 1172 | 1663 | |
{postgres=CT/postgres}
(1 row)
Okey, freezed, now launch vacuumdb from command line:
# vacuumdb -a -U postgres
vacuumdb: vacuuming database "postgres"
VACUUM
vacuumdb: vacuuming database "template1"
VACUUM
Opps, template1 must not be vacuumed.
select * from pg_database where datname='template1';
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit
| datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig |
datacl
-----------+--------+----------+---------------+--------------+-------------
-+---------------+--------------+--------------+---------------+-----------+
------------------------
template1 | 10 | 6 | t | t | -1
| 10792 | 1287 | 3221226760 | 1663 | |
{postgres=CT/postgres}
(1 row)
Unfreezed indeed.
"Olleg Samoylov" <olleg_s@mail.ru> writes:
> Opps, template1 must not be vacuumed.
Says who?
If we didn't vacuum template1 then it would be subject to XID wraparound
problems, unless it had never been modified, which is something vacuumdb
can't count on.
regards, tom lane
Tom Lane wrote: > "Olleg Samoylov" <olleg_s@mail.ru> writes: > >> Opps, template1 must not be vacuumed. > > > Says who? > > If we didn't vacuum template1 then it would be subject to XID > wraparound problems, unless it had never been modified, which is > something vacuumdb can't count on. template1 frozen by "vacuum freeze", thus can't be subject of XID wraparound problems, isn't it? man vacuum: > If this is done when there are no other open transactions in the same > database, then it is guaranteed that all tuples in the > database are ââfrozenââ and will not be subject to transaction > ID wraparound problems, no matter how long the database is left > unvacuumed. IMHO "vacuumdb -a" must don't vacuum database with datvacuumxid=datfrozenxid. -- Olleg Samoylov
Olleg Samoylov <olleg@mipt.ru> writes:
> IMHO "vacuumdb -a" must don't vacuum database with
> datvacuumxid=datfrozenxid.
That's not going to work because it will fail to detect whether the
database has been modified since the VACUUM FREEZE command.
In any case, what's the point? As long as you have a routine vacuuming
process in place, it doesn't really matter whether template1 gets
scanned. The only reason VACUUM FREEZE exists at all is to make it
possible to have a non-connectable, non-vacuumable template0.
regards, tom lane