Re: Leftover TEMPORARY tables?

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