Tom Lane wrote: > Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> writes: > >>>> No, it isn't. Please add a TODO item about it: > >>>> * Prevent long-lived temp tables from causing frozen-Xid advancement > >>>> starvation > >> > > Jeff Amiel wrote: > >> Can somebody explain this one to me? because of our auditing technique, we > >> have many LONG lived temp tables.....(one per pooled connection)...so as > >> long as the pool isn't disturbed, these temp tables can exist for a long > >> time (weeks....months?) > > > Hmm. The problem is that the system can't advance the frozen Xid for a > > database when there are temp tables that live for long periods of time. > > Autovacuum can't vacuum those tables; if the app vacuums them itself > > then there's no problem, but you can only vacuum them in the same > > session that creates it. > > I'm not convinced there's a huge problem here. Surely Jeff's app is > going to either vacuum or truncate those temp tables occasionally; > otherwise they'll bloat to the point of uselessness. Either action > will fix the problem. > > The real issue is that the app has to remember to do that. Perhaps > a better TODO item would be > * Find a way to autovacuum temp tables > though I admit I have no clue how to do that without giving up most > of the performance advantages of temp tables. TODO updated: * Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
