Missing Schema

[James Neville]

Hi,

We've got a rather odd problem with our PG 7.3.2 production installation.
Everything is working fine on the surface, and has been for quite some
time.

Only recently, we have noticed the abscence of any schemas.
We're at a complete loss as to how this happened, or even when.

The databases we run are used purely for web applications, and are
generally only ever accessed locally.
It was only on debugging queries from a third party product that we
noticed their absence.

The product issues queries like:
SELECT * FROM dbname.public.users;

Obviously, the JDBC Driver making the SQL query fails on 'public'.

We have another installation on a development machine that was compiled
from source at the same time.
This machine seems to be OK, so I doubt the production installation
failed in any way.

Does anyone have a clue as to how this may have happened, and an
approach to remedying the problem with a reinstall?
Many thanks in advance,

James.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Peter Eisentraut]

Post by James Neville
Does anyone have a clue as to how this may have happened, and an
approach to remedying the problem with a reinstall?

Well, perhaps you have simply dropped them? You can just recreate them.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[James Neville]

Post by Peter Eisentraut
Well, perhaps you have simply dropped them? You can just recreate them.

Well, thats a relief, but what tables do I need to recreate, and what
are their schemas?
I'm also rather curious as to how/why someone would have dropped a
system table.

Thanks,
James.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Peter Eisentraut]

Post by James Neville

Post by Peter Eisentraut
Well, perhaps you have simply dropped them? You can just recreate them.

Well, thats a relief, but what tables do I need to recreate, and what
are their schemas?

You don't need to create any tables, just schemas.

Post by James Neville
I'm also rather curious as to how/why someone would have dropped a
system table.

You didn't drop any system table (or you'd have far worse problems), you
(possibly) just dropped the schema "public".


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[James Neville]

Post by Peter Eisentraut
You don't need to create any tables, just schemas.

Thanks for the help Peter, i've since resolved the problem.
The production install was in fact, 7.2.3, not 7.3.2 as I stated
earlier, my bad.

It appears that PG only started supporting schemas since 7.3.x.
I was assumming that schema names were stored in a system table
somewhere, which, upon further research, are not. My bad.
Never make assumptions :)

Been messing around with 7.4.1 since (as said i'm due an update). Its
long overdue.
Now to find a JDBC driver that lets me set Auto Commit off without
raising exceptions.

But that, as they say, is a different story.....

Cheers,
James.



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Tom Lane]

Post by James Neville
Only recently, we have noticed the abscence of any schemas.

It's not possible that the database contains *no* schemas. You are
being way too imprecise in describing the problem.

Post by James Neville
SELECT * FROM dbname.public.users;
Obviously, the JDBC Driver making the SQL query fails on 'public'.

What's obvious about it? And what exactly is the error message?

I suspect you are completely misinterpreting what you are seeing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org