Thread: upgraded to 7.3.2 and now see pg_temp_x databases?

I upgraded from 7.2 to 7.3.2 and everything works great.  However, when I
open pgAdminII, I  see three empty databases named pg_temp_2,pg_temp_4 and
pg_temp_8.  What are these, and are they safe to remove?  I assumed they
were created as temp space while the database was doing one of its nightly
batch jobs. I also assumed it would disappear if I did a vacuum full on the
database, but they are still there.

TIA

Patrick Hatcher

"Patrick Hatcher" <PHatcher@macys.com> writes:
> I upgraded from 7.2 to 7.3.2 and everything works great.  However, when I
> open pgAdminII, I  see three empty databases named pg_temp_2,pg_temp_4 and
> pg_temp_8.  What are these, and are they safe to remove?

Those are schemas, not databases; they are created to hold temp tables.
While you can remove 'em if you wish (and if they're currently empty;
don't do a DELETE CASCADE!), there's no point in it, because they'll be
recreated as soon as backends need to make temp tables.

An empty schema doesn't cost anything except one row in pg_namespace,
so it didn't seem worthwhile to remove the schema only to have to make
it again later.  Instead, empty temp schemas are left around for re-use
by future backends.

            regards, tom lane

Thank you for the clarification.

Patrick Hatcher





                    Tom Lane
                    <tgl@sss.pgh.p       To:     "Patrick Hatcher" <PHatcher@macys.com>
                    a.us>                cc:     pgsql-general@postgresql.org
                                         Subject:     Re: [GENERAL] upgraded to 7.3.2 and now see pg_temp_x
                    03/21/2003            databases?
                    11:40 AM





"Patrick Hatcher" <PHatcher@macys.com> writes:
> I upgraded from 7.2 to 7.3.2 and everything works great.  However, when I
> open pgAdminII, I  see three empty databases named pg_temp_2,pg_temp_4
and
> pg_temp_8.  What are these, and are they safe to remove?

Those are schemas, not databases; they are created to hold temp tables.
While you can remove 'em if you wish (and if they're currently empty;
don't do a DELETE CASCADE!), there's no point in it, because they'll be
recreated as soon as backends need to make temp tables.

An empty schema doesn't cost anything except one row in pg_namespace,
so it didn't seem worthwhile to remove the schema only to have to make
it again later.  Instead, empty temp schemas are left around for re-use
by future backends.

                               regards, tom lane