Re: Modifying pg_shadow? - Mailing list pgsql-novice

Wow. I learned a lot just now... You have me interested in these 'schemas' I
know they are new for 7.3, so where can I find more info on them? (I read
Section 2.8)

It now looks like I can't do what I intended and I'll have to create my own
master permission list table, in addition to posture's. But I'll ask it - is
there a way to get all pg_class info for all tables in all databases
(schemas)?

Reading the docs (2.8), this is what I gather:
--Setup:
1) create a database (D), fill with objects
2) create a schema (S)(inherits current database's objects)
--Apps:
1) Connect to database D
2) SET search_path TO S, public; (for not having to scope all sql to this
schema)
3) use the schema as if it were a separate database  i.e. (select
S.tablename -> select tablename, because of my previous step)

Now, what are the implications for:
backups - If I do a pg_dump, it'll dump all schemas? Can I dump just one?
creating tables in the schema - they stay in that schema only?
modifying the database from which the schema was created - modifies all
schemas descended from that database?
create table with my step #2 above - does it go in the schema or the
database?

I think that does it for now...


> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, September 09, 2003 12:24 PM
> To: Oliver Fromme
> Cc: Jason Hihn; pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Modifying pg_shadow?
>
>
> Oliver Fromme <olli@lurza.secnetix.de> writes:
> > There is none.  The system tables are always visible, no
> > matter which DB you're connected to.  That's why they are
> > system tables ...
>
> > You're always connected to one database.  A GRANT command
> > will affect only that database, nothing else.  Even when
> > you issue GRANT on system tables (which are visible in
> > every database), the change will only affect the database
> > you're connected to.
>
> To enlarge on that a little: for the most part, each database has its
> own copy of the system catalogs (created when CREATE DATABASE clones
> the contents of template1).  This is why when you create a table in
> one database, it's visible in pg_class in that database but not in other
> databases.  CREATE TABLE only affects the local copy of pg_class.
>
> The exception to this is the "shared" system catalogs pg_database,
> pg_shadow, pg_group.  There is only one cluster-wide copy of these
> tables (and their indexes).  That's why you can find out what other
> databases exist in the cluster, and why you can create users and
> groups that are valid across the cluster and not just in one database.
>
> If you try to do something like GRANT or REVOKE on a system catalog,
> you are modifying the local copy of pg_class, and so the effects
> are only visible in your current database.  This is true even if the
> catalog in question is one of the shared catalogs --- the *contents*
> of the shared catalogs are shared, but the metadata about them is
> not.
>
> The reason for "pg_dumpall -g" to exist is precisely that users and
> groups are cluster-wide.  Everything else (including the pg_database
> attributes of a particular database) is dumped by pg_dump acting on
> individual databases.  But it would not be useful for each such pg_dump
> run to dump CREATE USER/GROUP commands.  So pg_dumpall dumps those
> separately, then invokes pg_dump successively on each database.
>
> BTW: the separate-databases mechanism is invaluable for experimental or
> development work --- no matter how badly you screw up the contents of
> pg_class or pg_proc, you can only corrupt the database you are working
> in, and the rest of the cluster can sail along just fine.  But for most
> production scenarios, it's probably overkill; do you really need to copy
> all the system catalogs for each user?  I'd recommend looking at using
> multiple schemas within a single database, instead.  Schemas are much
> lighter-weight than databases.  They also allow controlled sharing of
> information, whereas in a multiple-databases installation there is no
> convenient way to access data from different databases.
>
>             regards, tom lane
>