Обсуждение: Understanding Schema's
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
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
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.
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
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
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!
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
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
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
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