Skip to content

Commit 87808ae

Browse files
committed
Allow the pgstat views to show toast tables as well as regular tables
(the stats system has always collected this info, but the views were filtering it out). Modify autovacuum so that over-threshold activity in a toast table can trigger a VACUUM of the parent table, even if the parent didn't appear to need vacuuming itself. Per discussion a month or so back about "short, wide tables".
1 parent 2498d82 commit 87808ae

File tree

6 files changed

+107
-44
lines changed

6 files changed

+107
-44
lines changed

src/backend/catalog/system_views.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.19 2005/08/13 19:02:33 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.20 2005/08/15 16:25:17 tgl Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -190,7 +190,7 @@ CREATE VIEW pg_stat_all_tables AS
190190
FROM pg_class C LEFT JOIN
191191
pg_index I ON C.oid = I.indrelid
192192
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
193-
WHERE C.relkind = 'r'
193+
WHERE C.relkind IN ('r', 't')
194194
GROUP BY C.oid, N.nspname, C.relname;
195195

196196
CREATE VIEW pg_stat_sys_tables AS
@@ -223,7 +223,7 @@ CREATE VIEW pg_statio_all_tables AS
223223
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
224224
pg_class X ON T.reltoastidxid = X.oid
225225
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
226-
WHERE C.relkind = 'r'
226+
WHERE C.relkind IN ('r', 't')
227227
GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
228228

229229
CREATE VIEW pg_statio_sys_tables AS
@@ -248,7 +248,7 @@ CREATE VIEW pg_stat_all_indexes AS
248248
pg_index X ON C.oid = X.indrelid JOIN
249249
pg_class I ON I.oid = X.indexrelid
250250
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
251-
WHERE C.relkind = 'r';
251+
WHERE C.relkind IN ('r', 't');
252252

253253
CREATE VIEW pg_stat_sys_indexes AS
254254
SELECT * FROM pg_stat_all_indexes
@@ -272,7 +272,7 @@ CREATE VIEW pg_statio_all_indexes AS
272272
pg_index X ON C.oid = X.indrelid JOIN
273273
pg_class I ON I.oid = X.indexrelid
274274
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
275-
WHERE C.relkind = 'r';
275+
WHERE C.relkind IN ('r', 't');
276276

277277
CREATE VIEW pg_statio_sys_indexes AS
278278
SELECT * FROM pg_statio_all_indexes

src/backend/postmaster/autovacuum.c

Lines changed: 89 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -10,15 +10,15 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.3 2005/08/11 21:11:44 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.4 2005/08/15 16:25:17 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
1717
#include "postgres.h"
1818

1919
#include <signal.h>
20-
#include <time.h>
2120
#include <sys/types.h>
21+
#include <time.h>
2222
#include <unistd.h>
2323

2424
#include "access/genam.h"
@@ -86,6 +86,7 @@ typedef struct autovac_dbase
8686
typedef struct autovac_table
8787
{
8888
Oid relid;
89+
Oid toastrelid;
8990
bool dovacuum;
9091
bool doanalyze;
9192
int vacuum_cost_delay;
@@ -101,8 +102,10 @@ static void process_whole_db(void);
101102
static void do_autovacuum(PgStat_StatDBEntry *dbentry);
102103
static List *autovac_get_database_list(void);
103104
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
104-
Form_pg_class classForm, Form_pg_autovacuum avForm,
105-
List **vacuum_tables);
105+
Form_pg_class classForm,
106+
Form_pg_autovacuum avForm,
107+
List **vacuum_tables,
108+
List **toast_table_ids);
106109
static void autovacuum_do_vac_analyze(List *relids, bool dovacuum,
107110
bool doanalyze, bool freeze);
108111

@@ -386,12 +389,21 @@ AutoVacMain(int argc, char *argv[])
386389

387390
if (db)
388391
{
392+
/*
393+
* Report autovac startup to the stats collector. We deliberately
394+
* do this before InitPostgres, so that the last_autovac_time will
395+
* get updated even if the connection attempt fails. This is to
396+
* prevent autovac from getting "stuck" repeatedly selecting an
397+
* unopenable database, rather than making any progress on stuff
398+
* it can connect to.
399+
*/
400+
pgstat_report_autovac(db->oid);
401+
389402
/*
390403
* Connect to the selected database
391404
*/
392405
InitPostgres(db->name, NULL);
393406
SetProcessingMode(NormalProcessing);
394-
pgstat_report_autovac();
395407
set_ps_display(db->name);
396408
ereport(LOG,
397409
(errmsg("autovacuum: processing database \"%s\"", db->name)));
@@ -538,6 +550,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
538550
HeapTuple tuple;
539551
HeapScanDesc relScan;
540552
List *vacuum_tables = NIL;
553+
List *toast_table_ids = NIL;
541554
ListCell *cell;
542555
PgStat_StatDBEntry *shared;
543556

@@ -558,9 +571,25 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
558571
classRel = heap_open(RelationRelationId, AccessShareLock);
559572
avRel = heap_open(AutovacuumRelationId, AccessShareLock);
560573

574+
/*
575+
* Scan pg_class and determine which tables to vacuum.
576+
*
577+
* The stats subsystem collects stats for toast tables independently
578+
* of the stats for their parent tables. We need to check those stats
579+
* since in cases with short, wide tables there might be proportionally
580+
* much more activity in the toast table than in its parent.
581+
*
582+
* Since we can only issue VACUUM against the parent table, we need to
583+
* transpose a decision to vacuum a toast table into a decision to vacuum
584+
* its parent. There's no point in considering ANALYZE on a toast table,
585+
* either. To support this, we keep a list of OIDs of toast tables that
586+
* need vacuuming alongside the list of regular tables. Regular tables
587+
* will be entered into the table list even if they appear not to need
588+
* vacuuming; we go back and re-mark them after finding all the
589+
* vacuumable toast tables.
590+
*/
561591
relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
562592

563-
/* Scan pg_class looking for tables to vacuum */
564593
while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
565594
{
566595
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
@@ -571,9 +600,9 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
571600
ScanKeyData entry[1];
572601
Oid relid;
573602

574-
/* Skip non-table entries. */
575-
/* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
576-
if (classForm->relkind != RELKIND_RELATION)
603+
/* Consider only regular and toast tables. */
604+
if (classForm->relkind != RELKIND_RELATION &&
605+
classForm->relkind != RELKIND_TOASTVALUE)
577606
continue;
578607

579608
/*
@@ -607,7 +636,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
607636
HASH_FIND, NULL);
608637

609638
test_rel_for_autovac(relid, tabentry, classForm, avForm,
610-
&vacuum_tables);
639+
&vacuum_tables, &toast_table_ids);
611640

612641
systable_endscan(avScan);
613642
}
@@ -625,6 +654,22 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
625654

626655
CHECK_FOR_INTERRUPTS();
627656

657+
/*
658+
* Check to see if we need to force vacuuming of this table because
659+
* its toast table needs it.
660+
*/
661+
if (OidIsValid(tab->toastrelid) && !tab->dovacuum &&
662+
list_member_oid(toast_table_ids, tab->toastrelid))
663+
{
664+
tab->dovacuum = true;
665+
elog(DEBUG2, "autovac: VACUUM %u because of TOAST table",
666+
tab->relid);
667+
}
668+
669+
/* Otherwise, ignore table if it needs no work */
670+
if (!tab->dovacuum && !tab->doanalyze)
671+
continue;
672+
628673
/* Set the vacuum cost parameters for this table */
629674
VacuumCostDelay = tab->vacuum_cost_delay;
630675
VacuumCostLimit = tab->vacuum_cost_limit;
@@ -643,7 +688,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
643688
* test_rel_for_autovac
644689
*
645690
* Check whether a table needs to be vacuumed or analyzed. Add it to the
646-
* output list if so.
691+
* appropriate output list if so.
647692
*
648693
* A table needs to be vacuumed if the number of dead tuples exceeds a
649694
* threshold. This threshold is calculated as
@@ -670,7 +715,8 @@ static void
670715
test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
671716
Form_pg_class classForm,
672717
Form_pg_autovacuum avForm,
673-
List **vacuum_tables)
718+
List **vacuum_tables,
719+
List **toast_table_ids)
674720
{
675721
Relation rel;
676722
float4 reltuples; /* pg_class.reltuples */
@@ -764,12 +810,10 @@ test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
764810
* will be reset too.
765811
*/
766812

767-
elog(DEBUG2, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
813+
elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
768814
RelationGetRelationName(rel),
769815
vactuples, vacthresh, anltuples, anlthresh);
770816

771-
Assert(CurrentMemoryContext == AutovacMemCxt);
772-
773817
/* Determine if this table needs vacuum or analyze. */
774818
dovacuum = (vactuples > vacthresh);
775819
doanalyze = (anltuples > anlthresh);
@@ -778,23 +822,40 @@ test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
778822
if (relid == StatisticRelationId)
779823
doanalyze = false;
780824

781-
if (dovacuum || doanalyze)
825+
Assert(CurrentMemoryContext == AutovacMemCxt);
826+
827+
if (classForm->relkind == RELKIND_RELATION)
782828
{
783-
autovac_table *tab;
829+
if (dovacuum || doanalyze)
830+
elog(DEBUG2, "autovac: will%s%s %s",
831+
(dovacuum ? " VACUUM" : ""),
832+
(doanalyze ? " ANALYZE" : ""),
833+
RelationGetRelationName(rel));
784834

785-
elog(DEBUG2, "will%s%s %s",
786-
(dovacuum ? " VACUUM" : ""),
787-
(doanalyze ? " ANALYZE" : ""),
788-
RelationGetRelationName(rel));
835+
/*
836+
* we must record tables that have a toast table, even if we currently
837+
* don't think they need vacuuming.
838+
*/
839+
if (dovacuum || doanalyze || OidIsValid(classForm->reltoastrelid))
840+
{
841+
autovac_table *tab;
789842

790-
tab = (autovac_table *) palloc(sizeof(autovac_table));
791-
tab->relid = relid;
792-
tab->dovacuum = dovacuum;
793-
tab->doanalyze = doanalyze;
794-
tab->vacuum_cost_limit = vac_cost_limit;
795-
tab->vacuum_cost_delay = vac_cost_delay;
843+
tab = (autovac_table *) palloc(sizeof(autovac_table));
844+
tab->relid = relid;
845+
tab->toastrelid = classForm->reltoastrelid;
846+
tab->dovacuum = dovacuum;
847+
tab->doanalyze = doanalyze;
848+
tab->vacuum_cost_limit = vac_cost_limit;
849+
tab->vacuum_cost_delay = vac_cost_delay;
796850

797-
*vacuum_tables = lappend(*vacuum_tables, tab);
851+
*vacuum_tables = lappend(*vacuum_tables, tab);
852+
}
853+
}
854+
else
855+
{
856+
Assert(classForm->relkind == RELKIND_TOASTVALUE);
857+
if (dovacuum)
858+
*toast_table_ids = lappend_oid(*toast_table_ids, relid);
798859
}
799860

800861
RelationClose(rel);

src/backend/postmaster/pgstat.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
* Copyright (c) 2001-2005, PostgreSQL Global Development Group
1515
*
16-
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.105 2005/08/11 21:11:44 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.106 2005/08/15 16:25:17 tgl Exp $
1717
* ----------
1818
*/
1919
#include "postgres.h"
@@ -651,18 +651,20 @@ pgstat_beterm(int pid)
651651
* pgstat_report_autovac() -
652652
*
653653
* Called from autovacuum.c to report startup of an autovacuum process.
654+
* We are called before InitPostgres is done, so can't rely on MyDatabaseId;
655+
* the db OID must be passed in, instead.
654656
* ----------
655657
*/
656658
void
657-
pgstat_report_autovac(void)
659+
pgstat_report_autovac(Oid dboid)
658660
{
659661
PgStat_MsgAutovacStart msg;
660662

661663
if (pgStatSock < 0)
662664
return;
663665

664666
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_AUTOVAC_START);
665-
msg.m_databaseid = MyDatabaseId;
667+
msg.m_databaseid = dboid;
666668
msg.m_start_time = GetCurrentTimestamp();
667669

668670
pgstat_send(&msg, sizeof(msg));

src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.298 2005/08/13 19:02:34 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.299 2005/08/15 16:25:18 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200508131
56+
#define CATALOG_VERSION_NO 200508151
5757

5858
#endif

src/include/pgstat.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
* Copyright (c) 2001-2005, PostgreSQL Global Development Group
77
*
8-
* $PostgreSQL: pgsql/src/include/pgstat.h,v 1.35 2005/08/11 21:11:49 tgl Exp $
8+
* $PostgreSQL: pgsql/src/include/pgstat.h,v 1.36 2005/08/15 16:25:18 tgl Exp $
99
* ----------
1010
*/
1111
#ifndef PGSTAT_H
@@ -384,7 +384,7 @@ extern void pgstat_bestart(void);
384384
extern void pgstat_ping(void);
385385
extern void pgstat_report_activity(const char *what);
386386
extern void pgstat_report_tabstat(void);
387-
extern void pgstat_report_autovac(void);
387+
extern void pgstat_report_autovac(Oid dboid);
388388
extern void pgstat_report_vacuum(Oid tableoid, bool shared,
389389
bool analyze, PgStat_Counter tuples);
390390
extern void pgstat_report_analyze(Oid tableoid, bool shared,

0 commit comments

Comments
 (0)