Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_temp..

pg_temp..

From
"Brian Baquiran"
Date:
I am cleaning up an old 6.5 postgres installation prior to migrating it over
to 7.0.3. Looking in pg_class, I see some tables named pg_temp.<PID>.1 thru
7. I assume these were temp tables that got left behind when a backend with
pid <PID> died. Is it safe to delete these rows from pg_class? What else
should I do to clean up before migrating everything to 7.0.3?

I also see old pg_temp* and pg_sorttemp* in the data directory. I guess I'll
be deleting those too.

Brian

Re: pg_temp..

From
Tom Lane
Date:
"Brian Baquiran" <brianb@edsamail.com> writes:
> I am cleaning up an old 6.5 postgres installation prior to migrating it over
> to 7.0.3. Looking in pg_class, I see some tables named pg_temp.<PID>.1 thru
> 7. I assume these were temp tables that got left behind when a backend with
> pid <PID> died. Is it safe to delete these rows from pg_class?

Yes, if the backend PIDs that created 'em aren't around anymore.  I'd
suggest DROP TABLE rather than a DELETE FROM pg_class.

> I also see old pg_temp* and pg_sorttemp* in the data directory. I guess I'll
> be deleting those too.

If you like.  pg_dump won't notice anything that hasn't got a pg_class
entry, so there's no urgent need for it.  Since you've evidently been
having some problems with 6.5, I do suggest a full bore pg_dumpall/
initdb/reload cycle rather than trying to squeeze by with pg_upgrade.

            regards, tom lane

Re: pg_temp..

From
"Brian Baquiran"
Date:
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> "Brian Baquiran" <brianb@edsamail.com> writes:
> > Looking in pg_class, I see some tables named pg_temp.<PID>.1 thru
> > 7. Is it safe to delete these rows from pg_class?
>
> Yes, if the backend PIDs that created 'em aren't around anymore.  I'd
> suggest DROP TABLE rather than a DELETE FROM pg_class.

Tried that. I get "ERROR:  class "pg_temp.6004.0" is a system catalog". Is
it because it starts with "pg_"?

> > I also see old pg_temp* and pg_sorttemp* in the data directory. I guess
I'll
> > be deleting those too.
>
> If you like.

Thanks Tom.

Brian

Re: pg_temp..

From
Tom Lane
Date:
"Brian Baquiran" <brianb@edsamail.com> writes:
> Is it safe to delete these rows from pg_class?
>>
>> Yes, if the backend PIDs that created 'em aren't around anymore.  I'd
>> suggest DROP TABLE rather than a DELETE FROM pg_class.

> Tried that. I get "ERROR:  class "pg_temp.6004.0" is a system catalog". Is
> it because it starts with "pg_"?

Um.  Yeah, probably so.  Sometimes that looks like a feature, and
sometimes it looks like a bug ;-)

            regards, tom lane