Newbie questions (pg_global, tablespace, pg_temp, ...)

On Wed, Jan 17, 2007 at 07:02:13PM +0530, Alexi Gen wrote:

Hello,

I'm new to postgreSQL (not databases) and trying to find my way arouund.
Have a couple of questions:

(a) In which dir is the pg_global stored?
Is it "C:\Program Files\PostgreSQL\8.2\data\global"?

Yes.

(b) Why is the pg_tablespace.spclocation column=null for pg_default &
pg_global?

Because they're fixed by the base location of the cluster, they don't
need to specified.

(c) When creating an object - if the tablespace attribute is not specified
- it is resolved as follows:
Index - Parent Table on which the index is being created.
Table - Tablespace of the database in which the table is being created.
Database - Tablespace of the template from which this database was created
(Ex: template1).

If the above is the method by which the tablespace name is resolved, when
does the default_tablespace variable get used?
Or Will the above come into the picture if default_tablespace=NULL?

It's the default if nothing has been specified. The docs say quite
clearly that the default_tablespace GUC overrides those rules you gave.

(d)
The following objects are shared between all databases in a cluster.
pg.catalog.pg_group: List of user groups (View).
pg.catalog.pg_shadow: List of valid users. (View)
pg.catalog.pg_database: List of databases in the cluster. (Table)
pg.catalog.pg_tablespace: List of tablespaces. (Table)

There are 33 tables & 33 Views in the pg_catalog schema.
Are there any other tables & views that are common to all databases in a
cluster?

Anything in the pg_global tablespace.

Why have cluster common objects sitting in each database in the cluster?

They only exist once, they are just visible from each database.

How can we differentiate between objects in pg_catalog?

By OID? I'm not sure of the question.

Those that are specific to the database and those that are common to the
cluster?

The stuff that's global is in pg_global, everything else is seperate.

(e) Can someone please point me to a document/URL that has information
about using temp tables in postgreSQL?
I have already read material that is available in the official PostgreSQL
documentation.
Want something more - the inner workings, pg_temp*, etc..

The docs cover all this, but you'll have to go to the section dealing
with technical details.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/