Thread: owner as namespace?
Under Microsoft SQL, a fully qualified table name is db.owner.tablename (well actually, linked_server.db.owner.table, but we won't go there :) Under Postgres, you can create schemas to simulate different databases within the one actual database, but as far as I can tell, the owner does not contribute to the namespace at all. I can't have two views with the same name in the same database but with different owners (eg mydb.james.myview and mydb.fred.myview). Can someone tell me if this is correct? Assuming I'm correct, how well might it work if I created schema's in the form db_owner, eg mydb_james and mydb_fred, to emulate this? There would also be the schema of the dbo which mydb_dbo, which is also searched given an unqualified table name. If I'm logged in as james, my search path would be mydb_james then mydb_dbo. ? Thanks James
On Thu, Feb 09, 2006 at 09:37:35AM +1100, James Harper wrote: > Under Microsoft SQL, a fully qualified table name is > db.owner.tablename (well actually, linked_server.db.owner.table, but we > won't go there :) > > Under Postgres, you can create schemas to simulate different databases > within the one actual database, but as far as I can tell, the owner does > not contribute to the namespace at all. I can't have two views with the > same name in the same database but with different owners (eg > mydb.james.myview and mydb.fred.myview). Can someone tell me if this is > correct? > > Assuming I'm correct, how well might it work if I created schema's in > the form db_owner, eg mydb_james and mydb_fred, to emulate this? There > would also be the schema of the dbo which mydb_dbo, which is also > searched given an unqualified table name. If I'm logged in as james, my > search path would be mydb_james then mydb_dbo. IIRC, the idea of a schema came from Oracle where the schema is the username. If you create a schema with the same name as the user what you describe above works. ie mydb.james.myview is in the james schema in the mydb database. BTW, here's one little known feature of PostgreSQL: $ psql template1 Welcome to psql 7.4.7, the PostgreSQL interactive terminal. template1=# select count(*) from template1.pg_catalog.pg_proc; count ------- 1655 (1 row) You can specify the database name in the table reference. You can only reference your own database ofcourse, but the syntax is accepted without complaint. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes: > IIRC, the idea of a schema came from Oracle where the schema is the > username. If you create a schema with the same name as the user what > you describe above works. ie mydb.james.myview is in the james schema > in the mydb database. Actually that isn't an Oracle-ism, it's a reasonable interpretation of the minimum requirements of the SQL standard: if you associate each user with a schema named after and owned by that user, you get the minimum spec behavior. It sounds like MSSQL is doing about the same thing. Our default search_path setting is set up to support this usage, btw. See "Usage Patterns" in the schema documentation. http://www.postgresql.org/docs/8.1/static/ddl-schemas.html regards, tom lane
> > Martijn van Oosterhout <kleptog@svana.org> writes: > > IIRC, the idea of a schema came from Oracle where the schema is the > > username. If you create a schema with the same name as the user what > > you describe above works. ie mydb.james.myview is in the james schema > > in the mydb database. > > Actually that isn't an Oracle-ism, it's a reasonable interpretation of > the minimum requirements of the SQL standard: if you associate each user > with a schema named after and owned by that user, you get the minimum > spec behavior. It sounds like MSSQL is doing about the same thing. > MSSQL has the dbname and username matrix of namespaces, pg only has the one. The problem I am trying to solve is where an application wants to make use of both the dbname and username combination of namespaces, but it looks like it will need to be emulated with the schema. The dbname under MSSQL is a bit different though, it is actually a completely separate database which may or may not interfere with transaction framing. I think that the only time it would is if you have to do a database restore. You then have to restore both to exactly the same point to ensure data consistency. Often though, another database is accessed purely to allow foreign lookups of remote application data, it isn't so often that an application would need to update data in two databases (although I have such an application under mssql :) > Our default search_path setting is set up to support this usage, btw. > See "Usage Patterns" in the schema documentation. > http://www.postgresql.org/docs/8.1/static/ddl-schemas.html > > regards, tom lane Thanks James