Jeff Amiel wrote: > > Bruce Momjian wrote: >>> >>> No, it isn't. Please add a TODO item about it: >>> * Prevent long-lived temp tables from causing frozen-Xid advancement >>> starvation > > 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. The problem with a frozen Xid (datfrozenxid) that doesn't advance is of Xid-wraparound nature. The system eventually shuts itself down to prevent data loss, so if those temp tables live a really long life, you could be subject to that. (The immediate symptom is that pg_clog segments do not get recycled, which is not serious because it's just wasted disk space, and it's not a lot). > (previous thread about our use of temp tables and autovacuum/xid issues) > http://archives.postgresql.org/pgsql-general/2007-01/msg00690.php > http://archives.postgresql.org/pgsql-general/2007-01/msg00691.php Sorry, I'm offline ATM and can't check those. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
