Hi all,
I just want to report that we had here almost exactly the same problem
as reported here:
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00134.php
The whole scenario repeated the same: production DB refused to work,
restarted in single user mode, run vacuum (few hours), postgres still
complaining and refuse to start, more web searching, found leaked
temporary tables (this time they were a few proper temporary tables
created by our application, no toast table), dropped them, problem
solved. Net effect: ~5 hours downtime affecting a few hundreds of our
customers...
Can this scenario be included on the doc page regarding routine
vacuuming:
http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html
If it would have been there, it would have saved us all this down time,
I could have just simply drop the leaked temp tables in the first
place...
In general, I found very little information in the docs about the ways
temporary tables work in postgres. There are a few gotchas about
temporary tables, a special page discussing temp tables would be nice to
have, and linked from all other places which currently discuss different
aspects of this topic.
One thing which I still don't know what is it exactly doing is vacuuming
a temporary table from a different session: it worked for me in the
sense it did not throw any error, but it did nothing to the temp table
as far as I can tell... is there a way to vacuum/analyze temporary
tables from another session ? The docs definitely don't say anything
about this topic... I would think it can't work if the table lives in
fact in private memory of it's session, but it would be nice if the docs
would state these things clearly...
In fact I could attempt to write that page but need guidance.
Cheers,
Csaba.
