Thread: Problem: Temporary tables not really temporary tables
Hello,
I’ve got a little problem with my postgresql/postgis server here: Sometimes, temporary tables (sessions?) seem to “survive”, which in the end leads to a shared memory error, that can only be resolved by restarting (for a moment). This stops the shared memory error for a moment, but the sessions (and with them, the tables) are still there, even if there aren’t any open connections left.
Ironically, rebooting the server completely does NOT resolve it all, only restarting the database does. I assume that the error occurs when the connection to a client closes incorrectly or something like that…
In the end I have to clean up the whole mess manually with…
drop schema pg_temp_<sess_id> cascade;
But how can I prevent such sessions to survive or at least have them cleaned up automatically?
Regards,
Flo
Florian Schaetz <Florian.Schaetz@Optitool.DE> writes:
> I've got a little problem with my postgresql/postgis server here: Sometimes, temporary tables (sessions?) seem to
"survive",which in the end leads to a shared memory error, that can only be resolved by restarting (for a moment). This
stopsthe shared memory error for a moment, but the sessions (and with them, the tables) are still there, even if there
aren'tany open connections left.
What PG version is that? Can you create a reproducible test case?
(It doesn't have to be "reproduces every time"; something like "run this
program long enough and it'll eventually fail" would be fine.)
We've heard a few previous reports of such things, but nobody ever
had it happen regularly enough to have a chance of isolating the cause.
So it sounds like you're doing something that increases the probability
greatly, and if you could bottle that, it'd be really helpful ...
regards, tom lane
Florian Schaetz wrote > Hello, > > I've got a little problem with my postgresql/postgis server here: > Sometimes, temporary tables (sessions?) seem to "survive", which in the > end leads to a shared memory error, that can only be resolved by > restarting (for a moment). This stops the shared memory error for a > moment, but the sessions (and with them, the tables) are still there, even > if there aren't any open connections left. > > Ironically, rebooting the server completely does NOT resolve it all, only > restarting the database does. I assume that the error occurs when the > connection to a client closes incorrectly or something like that... > > In the end I have to clean up the whole mess manually with... > > drop schema pg_temp_ > <sess_id> > cascade; > > But how can I prevent such sessions to survive or at least have them > cleaned up automatically? The first thing to do is let the computer speak to us directly instead of you paraphrasing what it is saying to you. Error messages, schema information and the like are all necessary pieces of data it needs to tell us. It should also introduce itself and tell us where it lives. David J. -- View this message in context: http://postgresql.nabble.com/Problem-Temporary-tables-not-really-temporary-tables-tp5837328p5837377.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.