> So table file size zero bytes (seems autovacuum truncated that table to 0
> bytes).
Hmmm .... something did, but I see no clear evidence that it was
autovacuum.
Do you know why the mod date on the file is 2012-02-20 12:04? That's
more than two days after the error in your logs, so it's not clear to me
that the current state of the file tells us much about what happened on
the 17th. If autovacuum had truncated the table then, and the table
wasn't touched otherwise, the file mod date shouldn't have increased.
regards, tom lane
Hi,
Cron was attempt to populate the table once per hour after that problem happened.
And each time it was produced the same error.
So table had attempted write activity after error happen:
(
2012-02-18 00:36:01 MSK 27743 hh.app@hh from [local] [vxid:95/19965879 txid:2342867908] [INSERT] ERROR: could not read block 171 in file "base/16404/118881486": read only 0 of 8192 bytes
2012-02-18 00:36:01 MSK 27743 hh.app@hh from [local] [vxid:95/19965879 txid:2342867908] [INSERT] STATEMENT: insert into agency_statistics (employer_id, area_id, area_path, professional_area_id, vacancies_number) select e.employer_id, vb.area_id, a.path, s.professional_area_id, count(distinct v.vacancy_id) from vacancy v inner join employer e on v.employer_id = e.employer_id inner join vacancy_body vb on v.vacancy_body_id = vb.vacancy_body_id inner join vacancy_body_specialization vs on vb.vacancy_body_id = vs.vacancy_body_id inner join specialization s on s.specialization_id = vs.specialization_id inner join area a on vb.area_id = a.area_id where v.archived_status in (0) and v.disabled = false and e.category = $1 and e.state = $2 and e.manager_id > 0 group by e.employer_id, vb.area_id, a.path, s.professional_area_id
...
and so on until Monday when I was informed about that problem.
).
For information the table has quite simple structure without any unusual features:
hh=# \d+ agency_statistics_old
Table "public.agency_statistics_old"
Column | Type | Modifiers | Storage | Description
----------------------+------------------------+-----------+----------+-------------
employer_id | integer | not null | plain |
area_id | integer | not null | plain |
area_path | character varying(255) | not null | extended |
professional_area_id | integer | not null | plain |
vacancies_number | integer | not null | plain |
rank | integer | | plain |
normalised_rank | integer | | plain |
Indexes:
"agency_statistics_pkey" PRIMARY KEY, btree (employer_id, area_id, professional_area_id) CLUSTER
Has OIDs: no
Options: fillfactor=50
I almost sure I will get an error if I try insert something to the
agency_statistics_old.
Should I perform that test?
Kind Regards,
Maxim