Leftover TEMPORARY tables?
Leftover TEMPORARY tables?
От:
jboes@nexcerpt.com (Jeff Boes)
Дата:
I just noticed that there are a number of tables in our schema with names fo the form pg_temp_*; these correspond to temporary tables created by our application, but they shouldn't still exist. Under what circumstances would a temporary table become permanent? Would it happen if the backend exited abnormally? Is there any way, short of bouncing the database, to determine which tables are NOT in use by the application?
Re: Leftover TEMPORARY tables?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
jboes@nexcerpt.com (Jeff Boes) writes: > I just noticed that there are a number of tables in our schema with > names fo the form pg_temp_*; these correspond to temporary tables > created by our application, but they shouldn't still exist. Under what > circumstances would a temporary table become permanent? In theory, never. Do you have frequent backend crashes? Is it possible that the backend sessions that created these tables are actually still running? If you're quite certain that the sessions that created those tables are dead, it is safe to issue DROPs against them. I am not sure whether you have to jump through any hoops to do so though --- the protection checks against dropping system tables look for "pg_XXX" and so are likely to mutiny :-(. You might need to run a standalone backend with the -O command-line switch to get around that protection check. PG 7.3 handles this scenario a little better, but I take it you haven't upgraded yet ... regards, tom lane
Re: Leftover TEMPORARY tables?
От:
Andrew Biagioni <andrew.biagioni@e-greek.net>
Дата:
I'm running 7.3.1 and I seem to get a fair number of these as well. I have no
problems removing them, possibly because the processes associated with them are
really gone.
My best estimate as to when they appear is when I abort a transaction in
PGAdminII by killing the corresponding process on the server ("kill -9 XXXX"),
but that alone doesn't seem to account for all of them. I DEFINITELY never
have backend crashes - steady as a rock, and I love it for that :-).
This begs the question: what is a more elegant way to kill a runaway PGAdminII
transaction, when PGAdminII is running on Windows and I don't want to kill
PGAdminII itself (because it's executing a complex query I don't want to re-
write from scratch)?
Thanks,
Andrew
4/23/03 11:51:07 PM, Tom Lane wrote:
>jboes@nexcerpt.com (Jeff Boes) writes:
>> I just noticed that there are a number of tables in our schema with
>> names fo the form pg_temp_*; these correspond to temporary tables
>> created by our application, but they shouldn't still exist. Under what
>> circumstances would a temporary table become permanent?
>
>In theory, never. Do you have frequent backend crashes? Is it possible
>that the backend sessions that created these tables are actually still
>running?
>
>If you're quite certain that the sessions that created those tables are
>dead, it is safe to issue DROPs against them. I am not sure whether you
>have to jump through any hoops to do so though --- the protection
>checks against dropping system tables look for "pg_XXX" and so are
>likely to mutiny :-(. You might need to run a standalone backend with
>the -O command-line switch to get around that protection check.
>
>PG 7.3 handles this scenario a little better, but I take it you haven't
>upgraded yet ...
>
> regards, tom lane
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
Re: Leftover TEMPORARY tables?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andrew Biagioni writes:
> My best estimate as to when they appear is when I abort a transaction in
> PGAdminII by killing the corresponding process on the server ("kill -9 XXXX"),
Yikes. You're not serious are you?
That would certainly cause the backend to fail to clean up its temp
tables... not to mention that the postmaster will treat this as a
system crash and force all the other backends to punt too. Not the way
I'd pick to cancel a misbehaving query.
Try something less invasive next time, like kill -INT.
regards, tom lane
Re: Leftover TEMPORARY tables?
От:
Jeff Boes <jboes@nexcerpt.com>
Дата:
On Wed, 2003-04-23 at 23:51, Tom Lane wrote:
> In theory, never. Do you have frequent backend crashes? Is it possible
> that the backend sessions that created these tables are actually still
> running?
Not "frequent", although I suppose that depends on what you mean. We get
this error:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
about every 2 weeks or so ... I think that's where the leftover tables
come from. The tables persist through a database shutdown and restart.
>
> If you're quite certain that the sessions that created those tables are
> dead, it is safe to issue DROPs against them. I am not sure whether you
> have to jump through any hoops to do so though --- the protection
> checks against dropping system tables look for "pg_XXX" and so are
> likely to mutiny :-(. You might need to run a standalone backend with
> the -O command-line switch to get around that protection check.
>
No, the permissions were no problem: I DROPped them from a superuser
account. (I also found out, while doing this on a non-production
server, that if you DROP a temporary table that's owned by a LIVE
process, you can trigger the aforementioned Postmaster shared memory
complaint!)
> PG 7.3 handles this scenario a little better, but I take it you haven't
> upgraded yet ...
Nope, not yet ...
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
Re: Leftover TEMPORARY tables?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Jeff Boes writes: > On Wed, 2003-04-23 at 23:51, Tom Lane wrote: >> In theory, never. Do you have frequent backend crashes? > Not "frequent", although I suppose that depends on what you mean. We get > this error: > The Postmaster has informed me that some other backend > died abnormally and possibly corrupted shared memory. > about every 2 weeks or so ... I think that's where the leftover tables > come from. The tables persist through a database shutdown and restart. Yeah, if a backend crashes then there is no mechanism that can cause its temp tables to go away (before 7.3 anyway). Also, because a crash in one backend is a crash in all, thanks to the postmaster's scorched-earth approach to crash recovery, you could be leaking temp tables from backends that didn't have anything directly to do with the crash. So I think the above observation explains your leaked temp tables. But a backend crash every two weeks is too often for my taste. Have you tried to gather any more info about the crashes? A stack backtrace from the core dump would be valuable information. (If you don't get core dumps, it's probably because the postmaster is started under "ulimit -c 0", which is the default setting on far too many Unixen. Try putting "ulimit -c unlimited" into the script that starts the postmaster.) regards, tom lane
Re: Leftover TEMPORARY tables?
От:
Andrew Biagioni <andrew.biagioni@e-greek.net>
Дата:
4/24/03 9:28:49 AM, Tom Lane wrote:
>Andrew Biagioni writes:
>> My best estimate as to when they appear is when I abort a transaction in
>> PGAdminII by killing the corresponding process on the server ("kill -9
XXXX"),
>
>Yikes. You're not serious are you?
Sorry, forget the " -9 " bit. I only use that when I need to shut down
PostgreSQL and some process is not releasing at that time.
>That would certainly cause the backend to fail to clean up its temp
>tables... not to mention that the postmaster will treat this as a
>system crash and force all the other backends to punt too. Not the way
>I'd pick to cancel a misbehaving query.
With my "kill [procnum]" I don't seem to have any negative side effects, except
apparently for the temp tables (I _think_). Does that seem reasonable?
>Try something less invasive next time, like kill -INT.
>
> regards, tom lane
Thanks,
Andrew
Re: Leftover TEMPORARY tables?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andrew Biagioni writes: > With my "kill [procnum]" I don't seem to have any negative side > effects, except apparently for the temp tables (I _think_). Does that > seem reasonable? No, a normal kill (SIGTERM) should run proc_exit callbacks, including temp-table cleanup. There must be something else going on. regards, tom lane
Re: Leftover TEMPORARY tables?
От:
Andrew Biagioni <andrew.biagioni@e-greek.net>
Дата:
4/24/03 12:22:03 PM, Tom Lane wrote: >Andrew Biagioni writes: >> With my "kill [procnum]" I don't seem to have any negative side >> effects, except apparently for the temp tables (I _think_). Does that >> seem reasonable? > >No, a normal kill (SIGTERM) should run proc_exit callbacks, including >temp-table cleanup. There must be something else going on. > > regards, tom lane OK, Option 2: I use an SSH connection to tunnel port 5432 from my Windows machine to the DB machine (Red Hat 7.3); occasionally the SSH connection drops, and so does the tunnel. Could that explain the temp tables? Thanks, Andrew Biagioni
Re: Leftover TEMPORARY tables?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andrew Biagioni writes: > OK, Option 2: I use an SSH connection to tunnel port 5432 from my Windows > machine to the DB machine (Red Hat 7.3); occasionally the SSH connection > drops, and so does the tunnel. Could that explain the temp tables? Nope, don't like that either. It might take a little while for the kernel to realize the connection is dead, but once it does, the backend will see EOF on its input and will clean up and exit. regards, tom lane
Re: Leftover TEMPORARY tables?
От:
Aschaffer@bloomberg.com
Дата:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I remember finding something a while back where if the client was killed the query would stay running on the backend, this was due to the handling of client disconnects in the backend. In short it wasn't doing a leapfrog I think it was called. This was discovered durring the Postrgres class with Bruce he should have more details as my memory is a bit foggy. Not sure if this could relate to the problem at all, but when you mentioned disconnects it came to mind. -----BEGIN PGP SIGNATURE----- Version: PGPfreeware 7.0.3 for non-commercial use iQA/AwUBPqlvwih4imLwvL+vEQKUcQCfUxThsIzm21WC5m5U9WBr0knxBrMAoKnk CY1ZaHLKDZvNPUA2v5GL4P6z =Hexq -----END PGP SIGNATURE-----