Re: [ADMIN] Excessive growth of pg_attribute and other system tables

Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
> On Thursday 31 March 2005 12:06 pm, Tom Lane wrote:
>> Steve, is your app in the habit of creating lots of temp tables
>> that are not dropped explicitly? That would explain why you are
>> getting bit more than other people.

> Yes, various processes create in total well over 100 temporary tables
> every hour. None of them are explicitly dropped.

Try this patch (it's against 8.0, but applies with some fuzz to 7.4
too).

regards, tom lane

Index: pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.87.4.1
diff -c -r1.87.4.1 pgstat.c
*** pgstat.c 25 Mar 2005 00:35:14 -0000 1.87.4.1
--- pgstat.c 31 Mar 2005 23:12:21 -0000
***************
*** 160,165 ****
--- 160,166 ----
static void pgstat_recvbuffer(void);
static void pgstat_exit(SIGNAL_ARGS);
static void pgstat_die(SIGNAL_ARGS);
+ static void pgstat_beshutdown_hook(int code, Datum arg);

static int pgstat_add_backend(PgStat_MsgHdr *msg);
static void pgstat_sub_backend(int procpid);
***************
*** 670,675 ****
--- 671,695 ----

pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_BESTART);
pgstat_send(&msg, sizeof(msg));
+
+ /*
+ * Set up a process-exit hook to ensure we flush the last batch of
+ * statistics to the collector.
+ */
+ on_proc_exit(pgstat_beshutdown_hook, 0);
+ }
+
+ /*
+ * Flush any remaining statistics counts out to the collector at process
+ * exit. Without this, operations triggered during backend exit (such as
+ * temp table deletions) won't be counted. This is an on_proc_exit hook,
+ * not on_shmem_exit, so that everything interesting must have happened
+ * already.
+ */
+ static void
+ pgstat_beshutdown_hook(int code, Datum arg)
+ {
+ pgstat_report_tabstat();
}