Re: VACUUM touching file but not updating relation - Mailing list pgsql-general

On 11 November 2011 00:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> On 14 October 2011 12:12, Thom Brown <thom@linux.com> wrote:
>>> I just noticed that the VACUUM process touches a lot of relations
>>> (affects mtime) but for one file I looked at, it didn't change.  This
>>> doesn't always happen, and many relations aren't touched at all.
>
> No immmediate ideas as to why the mtime would change if the file
> contents didn't.  It seems like there must be a code path that marked
> a buffer dirty without having changed it, but we're usually pretty
> careful about that.
>
>>> And I can't find out what this particular OID relates to
>>> either.
>
> Well, the generic method is
>
> regression=# select oid,relname from pg_class where relfilenode = 11946;
>  oid  |    relname
> -------+----------------
>  11563 | pg_toast_11561
> (1 row)
>
> This is a toast relation, so ...
>
> regression=# select oid,relname from pg_class where reltoastrelid = 11563;
>  oid  |   relname
> -------+--------------
>  11561 | sql_packages
> (1 row)
>
> So in my git-tip database, that relfilenode is
> information_schema.sql_packages' TOAST table.  However, such OID
> assignments aren't terribly stable in development tip, and it was almost
> certainly something different a month ago (especially since
> sql_packages' TOAST table is generally empty, and your file is not).
> So you'll need to check this for yourself to see what it was, assuming
> you still have that database around.  It's a safe bet that it was a
> system catalog or index or toast table belonging thereto, though, just
> based on the range of OIDs it's in.

No, I don't still have the database, but tried the same thing on a
pre-existing database and found a few files exhibiting the same
change.

I checked all files where the time stamp of the file had changed, but
had the same MD5 sum.  I used the list in the query you mentioned and
get:

test2=# select oid,relname from pg_class where relfilenode in

(11682,11692,11707,11708,11725,11726,11727,11728,11740,11743,11744,11751,11752,11757,11761,11764,11765,11771,11776,11777,11778,11795,11816,11817,11854,11855,11858,11861,11862,11865,11866,11869,11870,11873,11874,11901,11902);

 oid  |             relname
------+---------------------------------
 2664 | pg_constraint_conname_nsp_index
 2651 | pg_am_name_index
 2652 | pg_am_oid_index
 2756 | pg_amop_oid_index
 2757 | pg_amproc_oid_index
 2650 | pg_aggregate_fnoid_index
 2839 | pg_toast_2618_index
 2660 | pg_cast_oid_index
 3085 | pg_collation_oid_index
 3164 | pg_collation_name_enc_nsp_index
 2689 | pg_operator_oprname_l_r_n_index
 2754 | pg_opfamily_am_name_nsp_index
 2755 | pg_opfamily_oid_index
 2681 | pg_language_name_index
 2682 | pg_language_oid_index
 2692 | pg_rewrite_oid_index
 2693 | pg_rewrite_rel_rulename_index
 2673 | pg_depend_depender_index
 2674 | pg_depend_reference_index
 3608 | pg_ts_config_cfgname_index
 3712 | pg_ts_config_oid_index
 3609 | pg_ts_config_map_index
 3604 | pg_ts_dict_dictname_index
 3605 | pg_ts_dict_oid_index
 3606 | pg_ts_parser_prsname_index
 3607 | pg_ts_parser_oid_index
 3766 | pg_ts_template_tmplname_index
 3767 | pg_ts_template_oid_index
 3080 | pg_extension_oid_index
 2840 | pg_toast_2619
 2665 | pg_constraint_conrelid_index
 2666 | pg_constraint_contypid_index
 2667 | pg_constraint_oid_index
 3081 | pg_extension_name_index
(34 rows)

An additional VACUUM shows up no such changes except for the case of a
visibility map, although I suspect that's expected to happen.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company