Re: Tempdb

On Thu, 2006-08-10 at 10:52, Sharma;G.S. wrote:
> Hi,
> can anybody tell me the How temporary tablespace management is done by
> postgres .
> where the temdb is located , how to find out the space taken by tempdb

Slowly step away from the Oracle and everything will be OK. :)

Seriously though, PostgreSQL doesn't use a separate temporary database
for temp stuff, it uses a set of temporary schemas for temporary things.

If you have temporary stuff, you'll see the schemas.

test=> create temporary table abc (a int);
CREATE TABLE

test=> \dn
List of schemas
Name | Owner
--------------------+----------
daily20051021 | postgres
information_schema | postgres
pg_catalog | postgres
pg_temp_3 | postgres
pg_toast | postgres
public | smarlowe
(8 rows)

You can change your search path to one:

test=> set search_path='pg_temp_1';
SET
test=> \d
List of relations
Schema | Name | Type | Owner
-----------+------+-------+----------
pg_temp_3 | abc | table | smarlowe
(1 row)

Note that tablespace management (quotas, etc.) are MUCH more primitive
in PostgreSQL than in Oracle.

This partly reflects the eras in which the two dbs grew up and what they
were doing.

Oracle grew up when 500 Meg RAID arrays were quite large, and space was
expensive, and controlling the use of it was important.

PostgreSQL "grew up" much later, when 500 Meg hard drives were door
stops and controlling the use of disk space was often more bother than
just adding on more drives to an array.

Not that there's no need for quotes in postgresql, there is. It's just
not been a priority for anyone to jump into.

Now that we have table spaces, you can use those to control space usage
by putting different things on differently sized drive arrays. Still
kinda primitive, but it works.