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