Skip site navigation (1) Skip section navigation (2)

Re: Unreferenced temp tables disables vacuum to update xid

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Unreferenced temp tables disables vacuum to update xid
Date: 2008-06-27 14:03:30
Message-ID: 1214575410.21997.197.camel@PCD12478 (view raw or flat)
Thread:
Lists: pgsql-general
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.



pgsql-general by date

Next:From: Rodrigo GonzalezDate: 2008-06-27 14:05:21
Subject: Re: ERROR: could not open relation with OID 2836
Previous:From: hubert depesz lubaczewskiDate: 2008-06-27 11:16:22
Subject: freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group