tablespaces and schemas

[Dennis Gearon]

This post is as much about getting some questions answered as leaving
the following definitions in the archives for the next person.

After a quick perview of the web, I came up with the following:

tablespaces are a hardware issue, and totally transparent to SQL
execution. It is for optimization for IO, recovery, and separating user
and application usage amongst disks even in the same databases.

schemas are a logical issue, and NOT transparent to the SQL. If schemas
are involved, the SQL needs to know which schema tables are in to access
them.

My questions are:
1/ Am I right/
2/ is the use of the '.' character standard across all databases as
a schema delimiter, i.e. SELECT * FROM {schemaname.tablename.columnname;} ?
3/ Once a user/dba gets down to the actual SQL, and past all the
bl***ng Oracle Obfuscation(TM), does Oracle do the same thing with
schemas that Postgres does, i.e. the aforementioned '.' separator?

I am building an application that I want to work on Postgres, IBM DB2,
Oracle, MSSQL, et.al.

TIA, y'all.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Andrew Rawnsley]

On Jun 9, 2004, at 5:15 PM, Dennis Gearon wrote:

This post is as much about getting some questions answered as leaving
the following definitions in the archives for the next person.

After a quick perview of the web, I came up with the following:

tablespaces are a hardware issue, and totally transparent to SQL
execution. It is for optimization for IO, recovery, and separating
user and application usage amongst disks even in the same databases.

A bit more like database configuration based upon your hardware/design
requirements and availability, but yes, its transparent to the guy
writing the SQL. In Oracle (Sorry to use the 'O' word on the list...),
you specify a tablespace when you create a table (or it uses a default
one), but after that it only matters to the DBA actually running the
installation.
schemas are a logical issue, and NOT transparent to the SQL. If
schemas are involved, the SQL needs to know which schema tables are in
to access them.

Yep.
My questions are:
1/ Am I right/
As much as makes no odds, yes.
2/ is the use of the '.' character standard across all databases as
a schema delimiter, i.e. SELECT * FROM
{schemaname.tablename.columnname;} ?
Yep.
3/ Once a user/dba gets down to the actual SQL, and past all the
bl***ng Oracle Obfuscation(TM), does Oracle do the same thing with
schemas that Postgres does, i.e. the aforementioned '.' separator?
Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work. See your Oracle Obfuscation(TM) documentation, which
of course will tell you nothing without the decoder ring that comes
with a $10,000 service contract), and a schema in Postgres. Sybase and
DB2 IIRC float in the middle with the terminology, but again, same
effect to the author (the poor sod actually implementing the thing has
to pay attention to all the differences, of course).

I am building an application that I want to work on Postgres, IBM DB2,
Oracle, MSSQL, et.al.

If you keep your SQL generic, its not really that hard to do if you
have/write decent middleware. The temptation is always to cheat and
take advantage of native doodads to help things along.
TIA, y'all.

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Andrew Rawnsley]

On Jun 9, 2004, at 5:15 PM, Dennis Gearon wrote:

This post is as much about getting some questions answered as leaving
the following definitions in the archives for the next person.

After a quick perview of the web, I came up with the following:

tablespaces are a hardware issue, and totally transparent to SQL
execution. It is for optimization for IO, recovery, and separating
user and application usage amongst disks even in the same databases.

A bit more like database configuration based upon your hardware/design
requirements and availability, but yes, its transparent to the guy
writing the SQL. In Oracle (Sorry to use the 'O' word on the list...),
you specify a tablespace when you create a table (or it uses a default
one), but after that it only matters to the DBA actually running the
installation.
schemas are a logical issue, and NOT transparent to the SQL. If
schemas are involved, the SQL needs to know which schema tables are in
to access them.

Yep.
My questions are:
1/ Am I right/
As much as makes no odds, yes.
2/ is the use of the '.' character standard across all databases as
a schema delimiter, i.e. SELECT * FROM
{schemaname.tablename.columnname;} ?
Yep.
3/ Once a user/dba gets down to the actual SQL, and past all the
bl***ng Oracle Obfuscation(TM), does Oracle do the same thing with
schemas that Postgres does, i.e. the aforementioned '.' separator?
Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work. See your Oracle Obfuscation(TM) documentation, which
of course will tell you nothing without the decoder ring that comes
with a $10,000 service contract), and a schema in Postgres. Sybase and
DB2 IIRC float in the middle with the terminology, but again, same
effect to the author (the poor sod actually implementing the thing has
to pay attention to all the differences, of course).

I am building an application that I want to work on Postgres, IBM DB2,
Oracle, MSSQL, et.al.

If you keep your SQL generic, its not really that hard to do if you
have/write decent middleware. The temptation is always to cheat and
take advantage of native doodads to help things along.
TIA, y'all.

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Andrew Rawnsley <ro**@ravensfield.com> writes:

Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work.

Oracle is actually SQL-spec-compliant in this regard (or possibly I
should say the spec is Oracle-compliant, seeing that they probably
dictated these semantics...) The SQL spec is carefully written
so that an implementation that enforces one-to-one matching of
schemas and users is spec-compliant.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Andrew Rawnsley <ro**@ravensfield.com> writes:

Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work.

Oracle is actually SQL-spec-compliant in this regard (or possibly I
should say the spec is Oracle-compliant, seeing that they probably
dictated these semantics...) The SQL spec is carefully written
so that an implementation that enforces one-to-one matching of
schemas and users is spec-compliant.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[John Sidney-Woollett]

Actually, you *can* write your SQL to be ignorant of schemas (while
still using them because they are good).

In postgres, you specifiy a search path for the user you connect as and
make sure that have the appropriate access rights to objects in other
schemas, and that the other schemas exist in your search path so that
the objects can be located.

In Oracle the same effect is achieved using synonyms. If a (public)
synonym exists in your schema for an object in another schema then you
can reference the object by the synonym name - especially if you make
the synonym name the same as the destination object that it refers to.
(A synonym can be considered to be like a symbolic link in unix).

The upshot is that you can write your SQL to reference objects without
the schema prefix if you wish.

John Sidney-Woollett

Andrew Rawnsley wrote:

On Jun 9, 2004, at 5:15 PM, Dennis Gearon wrote:

This post is as much about getting some questions answered as leaving
the following definitions in the archives for the next person.

After a quick perview of the web, I came up with the following:

tablespaces are a hardware issue, and totally transparent to SQL
execution. It is for optimization for IO, recovery, and separating
user and application usage amongst disks even in the same databases.

A bit more like database configuration based upon your hardware/design
requirements and availability, but yes, its transparent to the guy
writing the SQL. In Oracle (Sorry to use the 'O' word on the list...),
you specify a tablespace when you create a table (or it uses a default
one), but after that it only matters to the DBA actually running the
installation.

schemas are a logical issue, and NOT transparent to the SQL. If
schemas are involved, the SQL needs to know which schema tables are
in to access them.

Yep.

My questions are:
1/ Am I right/

As much as makes no odds, yes.

2/ is the use of the '.' character standard across all databases
as a schema delimiter, i.e. SELECT * FROM
{schemaname.tablename.columnname;} ?

Yep.

3/ Once a user/dba gets down to the actual SQL, and past all the
bl***ng Oracle Obfuscation(TM), does Oracle do the same thing with
schemas that Postgres does, i.e. the aforementioned '.' separator?

Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work. See your Oracle Obfuscation(TM) documentation, which
of course will tell you nothing without the decoder ring that comes
with a $10,000 service contract), and a schema in Postgres. Sybase
and DB2 IIRC float in the middle with the terminology, but again, same
effect to the author (the poor sod actually implementing the thing has
to pay attention to all the differences, of course).

I am building an application that I want to work on Postgres, IBM
DB2, Oracle, MSSQL, et.al.

If you keep your SQL generic, its not really that hard to do if you
have/write decent middleware. The temptation is always to cheat and
take advantage of native doodads to help things along.

TIA, y'all.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[John Sidney-Woollett]

Actually, you *can* write your SQL to be ignorant of schemas (while
still using them because they are good).

In postgres, you specifiy a search path for the user you connect as and
make sure that have the appropriate access rights to objects in other
schemas, and that the other schemas exist in your search path so that
the objects can be located.

In Oracle the same effect is achieved using synonyms. If a (public)
synonym exists in your schema for an object in another schema then you
can reference the object by the synonym name - especially if you make
the synonym name the same as the destination object that it refers to.
(A synonym can be considered to be like a symbolic link in unix).

The upshot is that you can write your SQL to reference objects without
the schema prefix if you wish.

John Sidney-Woollett

Andrew Rawnsley wrote:

On Jun 9, 2004, at 5:15 PM, Dennis Gearon wrote:

This post is as much about getting some questions answered as leaving
the following definitions in the archives for the next person.

After a quick perview of the web, I came up with the following:

tablespaces are a hardware issue, and totally transparent to SQL
execution. It is for optimization for IO, recovery, and separating
user and application usage amongst disks even in the same databases.

A bit more like database configuration based upon your hardware/design
requirements and availability, but yes, its transparent to the guy
writing the SQL. In Oracle (Sorry to use the 'O' word on the list...),
you specify a tablespace when you create a table (or it uses a default
one), but after that it only matters to the DBA actually running the
installation.

schemas are a logical issue, and NOT transparent to the SQL. If
schemas are involved, the SQL needs to know which schema tables are
in to access them.

Yep.

My questions are:
1/ Am I right/

As much as makes no odds, yes.

2/ is the use of the '.' character standard across all databases
as a schema delimiter, i.e. SELECT * FROM
{schemaname.tablename.columnname;} ?

Yep.

3/ Once a user/dba gets down to the actual SQL, and past all the
bl***ng Oracle Obfuscation(TM), does Oracle do the same thing with
schemas that Postgres does, i.e. the aforementioned '.' separator?

Schemas are users in Oracle, but the net effect to the SQL author is
the same. 'SELECT * FROM SERVICES.USERS' is the same, just that
'SERVICES' is a user in oracle (although referred to as a schema, and
you have to do a 'CREATE SCHEMA AUTHORIZATION blablabla' to get
anything to work. See your Oracle Obfuscation(TM) documentation, which
of course will tell you nothing without the decoder ring that comes
with a $10,000 service contract), and a schema in Postgres. Sybase
and DB2 IIRC float in the middle with the terminology, but again, same
effect to the author (the poor sod actually implementing the thing has
to pay attention to all the differences, of course).

I am building an application that I want to work on Postgres, IBM
DB2, Oracle, MSSQL, et.al.

If you keep your SQL generic, its not really that hard to do if you
have/write decent middleware. The temptation is always to cheat and
take advantage of native doodads to help things along.

TIA, y'all.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)