Re: [ADMIN] Excessive growth of pg_attribute and other system tables - Mailing list pgsql-hackers

Steve Crawford <scrawford@pinpointresearch.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);
staticvoid 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(); }