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