Thread: Understanding Schema's

Understanding Schema's

From
Carlos Mennens
Date:
I've recently switched from MySQL & have read the documentation for
'schema's' however I guess I'm just not at that level or really daft
when it comes to database design.

http://www.postgresql.org/docs/current/static/ddl-schemas.html

I'm trying to understand the relation between actual databases &
tables but can't grasp the relation with schema's so I was wondering
if someone has an easy way of explaining this beyond the documentation
I linked above. I notice my fresh 9.0 install has a default schema
called 'public' which every newly database I create defaults to and I
also created two new schema's called 'fire' & 'ice' but from what I
have written above, obviously I don't have any understanding of how
they work.

I greatly appreciate any info and or help since I appear to be lost.

-Carlos

Re: Understanding Schema's

From
"Joshua D. Drake"
Date:
On Tue, 2010-12-14 at 19:08 -0500, Carlos Mennens wrote:
> I've recently switched from MySQL & have read the documentation for
> 'schema's' however I guess I'm just not at that level or really daft
> when it comes to database design.
>
> http://www.postgresql.org/docs/current/static/ddl-schemas.html
>
> I'm trying to understand the relation between actual databases &
> tables but can't grasp the relation with schema's so I was wondering
> if someone has an easy way of explaining this beyond the documentation
> I linked above. I notice my fresh 9.0 install has a default schema
> called 'public' which every newly database I create defaults to and I
> also created two new schema's called 'fire' & 'ice' but from what I
> have written above, obviously I don't have any understanding of how
> they work.
>
> I greatly appreciate any info and or help since I appear to be lost.

It works like this:

cluster/catalog->[N]_Databases->[N]_Schemas->[N]_Objects

You can cross query a schema but not a database.

So you can create:

create table fire.foo()
create table ice.foo()

And they are isolated from each other physically and logically but you
can query them both:

SELECT fire.*, ice.* join on (id)

(psuedo SQL of course)

Sincerely,

Joshua D. Drake


>
> -Carlos
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


Re: Understanding Schema's

From
Carlos Mennens
Date:
On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> You can cross query a schema but not a database.
>
> So you can create:
>
> create table fire.foo()
> create table ice.foo()
>
> And they are isolated from each other physically and logically but you
> can query them both:
>
> SELECT fire.*, ice.* join on (id)

Why would anyone in a random scenario want to have independent
schema's to cross query? I'm just trying to see how this would be
useful in any scenario.

Re: Understanding Schema's

From
Bill Moran
Date:
On Tue, 14 Dec 2010 19:20:37 -0500
Carlos Mennens <carlos.mennens@gmail.com> wrote:

> On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> > You can cross query a schema but not a database.
> >
> > So you can create:
> >
> > create table fire.foo()
> > create table ice.foo()
> >
> > And they are isolated from each other physically and logically but you
> > can query them both:
> >
> > SELECT fire.*, ice.* join on (id)
>
> Why would anyone in a random scenario want to have independent
> schema's to cross query? I'm just trying to see how this would be
> useful in any scenario.

SELECT * FROM public.userinfo
 JOIN dmv.license_info
  USING (user_id)
 JOIN concealed_carry.license_info
  USING (user_id)
 JOIN medical.license_info
  USING (user_id);

... as a (somewhat contrived) example.  Since all these different
things (motor vehicles, personal firearms, and medical doctors)
require licensing, one way to organize the data is to have a license_info
table for each type of license and put them in schemas.  In this case,
it's simply a namespacing tool.  However, the application I code for
at work has about 300 tables, and that namespacing comes in pretty
damn handy. (in our case, the medical schema also has a bunch of other
tables related to medical data)

Schemas can also be used for security purposes.  As an example, we have
a temp_table schema, and the general users are allowed to create tables
in that schema, but nowhere else.  This makes it rather easy to have a
cron job that comes through and cleans out old temp tables and temp
tables that don't conform to our naming conventions, since we don't have
to worry about it dancing around tables that aren't temp tables.

When you're starting out, an easy way to think of schemas is like
directories on an operating system.  It's not an exact analogy, but it
helps one to understand the purposes, benefits, etc.

-Bill

Re: Understanding Schema's

From
Craig Ringer
Date:
On 12/15/2010 08:08 AM, Carlos Mennens wrote:
> I've recently switched from MySQL&  have read the documentation for
> 'schema's' however I guess I'm just not at that level or really daft
> when it comes to database design.

In terms of the way they work and their operation, PostgreSQL "schemas"
are in many ways much more like MySQL "databases" than PostgreSQL
"databases" are. Schema separate objects (functions, tables, views,
types, etc) into namespaces, but can refer to each other if permissions
allow. You can GRANT and REVOKE access to schema the same way you can
MySQL databases. You can "change" schema using "SET search_path" in much
the same way you'd "USE" a database in MySQL.

PostgreSQL also has "databases" which are largely isolated from each
other. They cannot refer to objects in other databases, and a backend
connected to one database cannot switch to another one. You cannot "USE"
or otherwise change databases on a backend; the psql "\c" command that
appears to do this really disconnects and reconnects to a new database.

It'd be nice if PostgreSQL offered more convenient ways to set an
initial schema for new connections, because for some use cases it'd be
quite handy to use a single database with many schema. Unfortunately
most tools only know how to ask for a database name, not a schema name,
and there's no way to set the search_path in (AFAIK) a JDBC URL. So in
this way, PostgreSQL schema are very _unlike_ mysql databases.

--
Craig Ringer

Re: Understanding Schema's

From
Alban Hertroys
Date:
On 15 Dec 2010, at 3:14, Craig Ringer wrote:

> It'd be nice if PostgreSQL offered more convenient ways to set an initial schema for new connections, because for
someuse cases it'd be quite handy to use a single database with many schema. Unfortunately most tools only know how to
askfor a database name, not a schema name, and there's no way to set the search_path in (AFAIK) a JDBC URL. So in this
way,PostgreSQL schema are very _unlike_ mysql databases. 


I doubt you're unaware of this - you're a long-time user after all, but you can set the default search path per user.
So,if you (can) use different database users for different connections, the above is possible. 
There's also this (also per user):

development=> \h CREATE SCHEMA
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ .... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]


Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4d086e81802651124320305!



Re: Understanding Schema's

From
Jacqui Caren-home
Date:
On 15/12/2010 00:20, Carlos Mennens wrote:
> Why would anyone in a random scenario want to have independent
> schema's to cross query? I'm just trying to see how this would be
> useful in any scenario.

One very real example :-)

When migrating from say mysql to PgSQL it is possible to populate a "mygration" schema with functions, domains etc to
allow(some) 
native mysql SQL to run within PgSQL.

However such code does not give the best performance so the next step is to identify the mysql code in use (via
notifies?)then replace with new 
(hopefully more portable) SQL.

Once you stop getting notifies, you remove the mygration schema from the search path and run your test suite once more.

If all passes you know you are now mysql free :-)

---------------------------------------
When developing databases we often end up with hundreds of tables many of which are feature/application
specific with a core of common tables. schemas are not just handy but essential as by setting the search
path we define what an application can "see". not perfect and not a security feature but "handy"
especialy for developers.

One example is a a3c schema used for authentication, authorization and access control. My template schema
provides a generate users, roles, rights model for use within apps.

Jacqui

Re: Understanding Schema's

From
Jasen Betts
Date:
On 2010-12-15, Carlos Mennens <carlos.mennens@gmail.com> wrote:
> On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> You can cross query a schema but not a database.
>>
>> So you can create:
>>
>> create table fire.foo()
>> create table ice.foo()
>>
>> And they are isolated from each other physically and logically but you
>> can query them both:
>>
>> SELECT fire.*, ice.* join on (id)
>
> Why would anyone in a random scenario want to have independent
> schema's to cross query? I'm just trying to see how this would be
> useful in any scenario.

Suppose you're an ISP and want to run a mailserver using your user
database for athentications and dbmail for storage and also run
and a RADIUS server to authenticate your users.  You can put your user
list in one schema and put the freeradius tables in another but
substitute the freeradius user list with a view which references the
main userlist, then put dbmail in a third  with another view pointing
back to your userlist Your billing software could be in yet another schema.
etc...


Or perhaps you have a partitioned logging table that changes frequently
and you want to exclude it from backups,  if you put it in a separate
schema it can be easily excluded, else you'd have to liste the approx 100
partition tables for exclusion each time...





--
⚂⚃ 100% natural

Re: Understanding Schema's

From
Jasen Betts
Date:
On 2010-12-15, Craig Ringer <craig@postnewspapers.com.au> wrote:
> On 12/15/2010 08:08 AM, Carlos Mennens wrote:
>> I've recently switched from MySQL&  have read the documentation for
>> 'schema's' however I guess I'm just not at that level or really daft
>> when it comes to database design.
>
> In terms of the way they work and their operation, PostgreSQL "schemas"
> are in many ways much more like MySQL "databases" than PostgreSQL
> "databases" are. Schema separate objects (functions, tables, views,
> types, etc) into namespaces, but can refer to each other if permissions
> allow. You can GRANT and REVOKE access to schema the same way you can
> MySQL databases. You can "change" schema using "SET search_path" in much
> the same way you'd "USE" a database in MySQL.
>
> PostgreSQL also has "databases" which are largely isolated from each
> other. They cannot refer to objects in other databases, and a backend
> connected to one database cannot switch to another one. You cannot "USE"
> or otherwise change databases on a backend; the psql "\c" command that
> appears to do this really disconnects and reconnects to a new database.

> It'd be nice if PostgreSQL offered more convenient ways to set an
> initial schema for new connections, because for some use cases it'd be
> quite handy to use a single database with many schema.

the first schema on the search path is the one that matches the name
of the database user. (although you can override this behaviour using
alter user or alter database - basically any option you can set
temporarily using "set ... " you can set permanently using "alter user
... set ..." )

eg: alter user jasen set search_path to thatschema;

> Unfortunately most tools only know how to ask for a database name

they also ask for a user name, in most cases having the different
services connect using different roles is not a bad thing.

--
⚂⚃ 100% natural

Stitch Large Panorama

From
"Alfred"
Date:
Hi, there,

First of all, happy holidays to every one! I wish all of you the best in the coming 2011.

I recently released a large panorama on Gigapan at http://www.gigapan.org/gigapans/66626/ . This panorama received many
seriousdebate about the true pixel count. Since I used Autopano, my argument was, when there is no standard way to
calculatethe total pixel count, we should not manipulate the software output, rather, everyone use the same baseline.
Theneveryone should also provide theoretical estimation of the optical pixel count. In this case, 112G pixels.  

The questions here are:

1.    Is there a way to stitch super size panorama in PTGUI?  Can we emit raw files so that it is not subjected to
imageformat limit? 
2.    Is there a way to import Autopano config file and process in PTGUI?
3.    Can PTGUI accurately report the optical pixel count for super size panorama?

Thanks!

Cheers,
Alfred