Обсуждение: last_vacuum field is not updating
Why does vacuum table is not updating the field last_vacuum of pg_stat_user_tables?
select * from pg_stat_user_tables where relname = 'table1';
-[ RECORD 1 ]-----+------------------------------
relid | 5452445
schemaname | public
relname | table1
seq_scan | 54911
seq_tup_read | 373562142
idx_scan | 2773802
idx_tup_fetch | 125909227
n_tup_ins | 889595
n_tup_upd | 530602
n_tup_del | 529037
n_tup_hot_upd | 162199
n_live_tup | 360526
n_dead_tup | 0
last_vacuum | 2013-06-29 20:04:15.391413-04
last_autovacuum | 2013-05-01 00:55:01.970799-04
last_analyze | 2013-07-15 10:55:40.870926-04
last_autoanalyze | 2013-05-01 06:04:12.905961-04
vacuum_count | 92
autovacuum_count | 248
analyze_count | 94
autoanalyze_count | 560
psql# vacuum analyze verbose table1;
INFO: vacuuming "public.table1"
INFO: index "table1_pkey" now contains 360965 row versions in 1878 pages
DETAIL: 0 index row versions were removed.
326 index pages have been deleted, 305 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "table1": found 0 removable, 1116 nonremovable row versions in 30 out of 2286 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 6720 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
select * from pg_stat_user_tables where relname = 'table1';
-[ RECORD 1 ]-----+------------------------------
relid | 5452445
schemaname | public
relname | table1
seq_scan | 54911
seq_tup_read | 373562142
idx_scan | 2773802
idx_tup_fetch | 125909227
n_tup_ins | 889595
n_tup_upd | 530602
n_tup_del | 529037
n_tup_hot_upd | 162199
n_live_tup | 360526
n_dead_tup | 0
last_vacuum | 2013-06-29 20:04:15.391413-04
last_autovacuum | 2013-05-01 00:55:01.970799-04
last_analyze | 2013-07-15 10:55:40.870926-04
last_autoanalyze | 2013-05-01 06:04:12.905961-04
vacuum_count | 92
autovacuum_count | 248
analyze_count | 94
autoanalyze_count | 560
Hi Al, Il 15/07/2013 16:58, AI Rumman ha scritto: > Why does vacuum table is not updating the field last_vacuum of > pg_stat_user_tables? > To vacuum a table, one must ordinarily be the table's owner. However, database owners are allowed to vacuum all tables in their databases. VACUUM will skip over any tables that the calling user does not have permission to vacuum. Are you sure you are the table's owner? Regards, Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
On Mon, Jul 15, 2013 at 6:43 PM, Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it> wrote: > Are you sure you are the table's owner? It should not be a permission problem: it works even after a revoke all on 9.2.4. Interestingly also the autovacuum is really old. Have you tried to do a simple vacuum? From the documentation (http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE): Last time at which this table was manually vacuumed (not counting VACUUM FULL) Are you sure we are looking at the same table? Luca
Yes, I am sure that I am looking for the same table.
On Tue, Jul 16, 2013 at 4:34 AM, Luca Ferrari <fluca1978@infinito.it> wrote:
On Mon, Jul 15, 2013 at 6:43 PM, Giuseppe BroccoloIt should not be a permission problem: it works even after a revoke
<giuseppe.broccolo@2ndquadrant.it> wrote:
> Are you sure you are the table's owner?
all on 9.2.4. Interestingly also the autovacuum is really old. Have
you tried to do a simple vacuum? From the documentation
(http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE):
Last time at which this table was manually vacuumed (not counting VACUUM FULL)
Are you sure we are looking at the same table?
Luca
On Tue, Jul 16, 2013 at 3:22 PM, AI Rumman <rummandba@gmail.com> wrote: > Yes, I am sure that I am looking for the same table. > What if you analyze the table? Does the column on the stats get updated? Have you tested such behavior against another (even dummy) table? Luca