Re: [HACKERS] what is the meaning of "schema"?

My understanding of "schema" that I discovered
in 7.3 (I don't think they were available before)
is that you can have two tables with the same name
if they are in different schemas.

I have done a google search, as well as archive search
but

pg_dump and pg_dumpall are broken if a database
contains schemas.

First of all if there are two tables with the same
name in different schemas pg_dump only dumps out
one table. There is no way to dump other tables
and I have checked pg_dump man page

Restoring a pg_dumpall is now a nightmare because

I had as superuser

# create schema test authorization httpd

on a database not owned by database owner.
And it works merrily until the time to
dump and restore.

pg_dumpall answers to above create authorization is

\connect - httpd

create schema test

Hell breaks lose with that! Because httpd cannot
create schema on a database that it does not own.
Why couldn't pg_dumpall does

create schema test authorization httpd

as superuser when the schema was created in that
fashion?

I really don't think anyone is going to pay attention
to this rant since these list does not like/answer anonymous posts
but I have to post just so some poor soul might find
it in the archive and be warned.

My current versions are 7.3.2 and 7.3.3 and I have been using
posgres since 7.1 and consider myself experienced with postgres

Schemas are the best thing since slice breads but
the baker decided to poison the bread. Nice!

Thanks

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

On Friday 20 Jun 2003 4:19 pm, _ wrote:
> My understanding of "schema" that I discovered
> in 7.3 (I don't think they were available before)
> is that you can have two tables with the same name
> if they are in different schemas.
>
> I have done a google search, as well as archive search
> but
>
> pg_dump and pg_dumpall are broken if a database
> contains schemas.
>
> First of all if there are two tables with the same
> name in different schemas pg_dump only dumps out
> one table. There is no way to dump other tables
> and I have checked pg_dump man page

Works here - v7.3.2:

SET search_path = public, pg_catalog;
[begin pg_dump extract]
--
-- Data for TOC entry 25 (OID 2263656)
-- Name: bar; Type: TABLE DATA; Schema: public; Owner: richardh
--

COPY bar (m) FROM stdin;
1.00
2345.00
99999999.00
\.

SET search_path = richardh, pg_catalog;

--
-- Data for TOC entry 26 (OID 2275041)
-- Name: bar; Type: TABLE DATA; Schema: richardh; Owner: richardh
--

COPY bar (a, b) FROM stdin;
1 aaa
\.
[end pg_dump extract]

Did the user you pg_dumped as have visibility on your second schema?

> Restoring a pg_dumpall is now a nightmare because
>
> I had as superuser
>
> # create schema test authorization httpd
>
> on a database not owned by database owner.
> And it works merrily until the time to
> dump and restore.
>
> pg_dumpall answers to above create authorization is
>
> \connect - httpd
>
> create schema test
>
> Hell breaks lose with that! Because httpd cannot
> create schema on a database that it does not own.
> Why couldn't pg_dumpall does
>
> create schema test authorization httpd

Did you GRANT CREATE ON DATABASE for user httpd? That looks like what you
need.

> as superuser when the schema was created in that
> fashion?
>
> I really don't think anyone is going to pay attention
> to this rant since these list does not like/answer anonymous posts
> but I have to post just so some poor soul might find
> it in the archive and be warned.

Always thought of the lists as welcoming myself, although I must admit
anonymous posting is a bit odd. Especially when you could call yourself John
Smith and no-one would be any the wiser.

> My current versions are 7.3.2 and 7.3.3 and I have been using
> posgres since 7.1 and consider myself experienced with postgres
>
> Schemas are the best thing since slice breads but
> the baker decided to poison the bread. Nice!

Let me know if this reply helps
--
Richard Huxton