On Sun, Mar 13, 2011 at 11:59 PM, Gnanakumar <gnanam@zoniac.com> wrote:
[...]
Although it's unrelated to your query about an exception being raised,
a word of advice on temporary tables. (This may not be relevant to
your situation, but it's no small source of pain in one of the systems
I'm responsible for.) Significant use of temporary tables will cause
commensurately significant bloat in your system catalogs. We have a
system which, in the course of its "normal" operations, creates
hundreds of temporary tables for processing, aggregation, analysis,
and reporting.
Consequently, the database runs with significantly bloated system
catalogs, particularly pg_attribute, which, even with autovacuum
running, and tuned rather aggressively, as well as frequent scheduled
VACUUMs, reaches a steady-state of around 90% dead tuples.
This has adverse effects on query planning performance, among other
things. (psql's tab-completion taking forever to complete is the one
that keeps biting me, because it works everywhere else, so I
constantly forget not to do it in this db.) The only remedy is to
bring the application down, stop postgres and restart it in
single-user mode, and VACUUM FULL and REINDEX the affected system
catalogs.
I'm working with the development team to re-write the application to
use normal tables, which are TRUNCATEd after use, but until that can
be deployed, we're stuck with periodic downtime.
rls
--
:wq