Re: New VACUUM FULL

Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Fri, 2009-12-04 at 18:36 +0000, Simon Riggs wrote:
> > Let's check it works before worrying about performance. We can take
> > tests out as well as add them once it becomes obvious its working.
>
> Itagaki-san, perhaps you should add a variety of tests, and then Simon
> can remove extra tests after he's convinced that it works.

I added regression tests for database-wide vacuums and check changes
of relfilenodes in those commands. Only sampled tables are checked
in tests -- normal, fundamental and shared catalogs and clusterd,
temp and normal tables. Since relfilenodes are unstable between tests,
only changes of relfilenodes are compared.

Do you think the added tests are enough? Of course we could have
cases for serveral updated patterns, but it will be exhaustive.
I think checks for relfilenodes are enough in this case.

BTW, I needed to add ORDER BY cluase in select_views test. I didn't modify
tests in select_views at all, but database-wide vacuum moves tuples in
select_views test. I think the fix should be reasonable becausae unsorted
result set is always unstable in regression test.

---- added tests ----
CREATE TEMP TABLE vacid (
relid regclass,
filenode_0 oid,
filenode_1 oid,
filenode_2 oid,
filenode_3 oid
);
INSERT INTO vacid (relid, filenode_0)
SELECT oid, relfilenode FROM pg_class WHERE oid::regclass IN (
'pg_am', -- normal catalog
'pg_class', -- fundamental catalog
'pg_database', -- shared catalog
'vaccluster' , -- clustered table
'vacid', -- temp table
'vactst' -- normal table
);
CLUSTER; -- only clusterd table should be changed
UPDATE vacid SET filenode_1 = relfilenode
FROM pg_class WHERE oid = relid;
VACUUM (FULL INPLACE); -- all tables should not be changed
UPDATE vacid SET filenode_2 = relfilenode
FROM pg_class WHERE oid = relid;
VACUUM FULL; -- only non-system tables should be changed
UPDATE vacid SET filenode_3 = relfilenode
FROM pg_class WHERE oid = relid;
SELECT relid,
filenode_0 <> filenode_1 AS cluster,
filenode_1 <> filenode_2 AS full_inplace,
filenode_2 <> filenode_3 AS full
FROM vacid
ORDER BY relid::text;
relid | cluster | full_inplace | full
-------------+---------+--------------+------
pg_am | f | f | f
pg_class | f | f | f
pg_database | f | f | f
vaccluster | t | f | t
vacid | f | f | t
vactst | f | f | t
(6 rows)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center