On 11 November 2011 00:55, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Thom Brown <thom@xxxxxxxxx> writes: >> On 14 October 2011 12:12, Thom Brown <thom@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
