How is autovacuum affected by a change in year.

Поиск
Список
Период
Сортировка
От Hanns Hartman
Тема How is autovacuum affected by a change in year.
Дата
Msg-id CAO4T21-Dv5NrCYYN0bKiar0cybfo1zUgVbG=p8i-ee2CfNoYkA@mail.gmail.com
обсуждение исходный текст
Ответы Re: How is autovacuum affected by a change in year.  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hi,

I am running postgres 8.3.17 on a RedHat linux derivative using a mips64 architecture.

I've recently noticed some odd autovacuum behavior.

Recently we've had some systems deployed with the system clock set to the year 2016.  Postgres was installed with that date and things were fine until a user noticed the incorrect date.  They reset the system time back to 2015 and then we started seeing weird behavior where the autovacuum proccess does not seem to be maintaining the disk space.

The problematic table has 245 column 107 of which are of type text and the rest are int or bigint. The table usually has very few rows (50-100) which once inserted are static.

Almost all of the the table activity is update based. Every 60 seconds most columns of each row are updated.  The text columns in particular are updated quite frequently.

So this beings me to the issue.  After the date change from 2016->2015, the toast table for this table appears to be growing unbounded .  Running a VACUUM FULL of this table immediately frees up the disk space.  The adding the verbose option to the vacuum command shows that most of the tuples in the toast table were marked as dead and were easily removed.

I've tried restarting postgres and the server but nothing seems to make the autovacuum kick in.
The only thing that fixes the autovacuum is setting the date back to 2016.

Is the autovacuum process affected by a change in time/date/year? (I noticed in the code that the method launcher_determine_sleep a call is made to GetCurrentTimestamp)

When the year is changed backwards does postgres have to be restarted? (sorry if this is a stupid question but a restart seemed to have no affect)

Could the system year postgres was installed being different from the year its being run in have an affect on the autovacuum?

My postgresql.conf file is using the default vacuum configuration.

I also I see no autovacuum related error messages in the db log file.

thanks
-Hanns

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Triggers Operations
Следующее
От: Andrew Dunstan
Дата:
Сообщение: Re: "JSON does not support infinite date values"