Thread: Leftover TEMPORARY tables?
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?
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
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 <tgl@sss.pgh.pa.us> 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)
>
>
Andrew Biagioni <andrew.biagioni@e-greek.net> 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
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
Jeff Boes <jboes@nexcerpt.com> 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
4/24/03 9:28:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>Andrew Biagioni <andrew.biagioni@e-greek.net> 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
Andrew Biagioni <andrew.biagioni@e-greek.net> 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
4/24/03 12:22:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>Andrew Biagioni <andrew.biagioni@e-greek.net> 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
Andrew Biagioni <andrew.biagioni@e-greek.net> 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
-----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 <http://www.pgp.com> iQA/AwUBPqlvwih4imLwvL+vEQKUcQCfUxThsIzm21WC5m5U9WBr0knxBrMAoKnk CY1ZaHLKDZvNPUA2v5GL4P6z =Hexq -----END PGP SIGNATURE-----