Обсуждение: 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