Re: Invisible tables

Webb Sprague wrote:
>
> Hello all,
>
> In my quest to learn PG and SQL programming, I have
> created tables in a database "foo". I am able to
> insert, select, etc just fine, but when I use "\dt" to
> show them they don't appear. They ARE listed in the
> system table "pg_tables", however.

This sounds to me like the owner of the tables no longer exists.
Perform a select on pg_class:

SELECT relname, relowner FROM pg_class WHERE relname = 'foo';

where 'foo' is the name of one of the tables you can no longer
view with \dt. Once you have that information, peform the
following select:

SELECT usename, usesysid FROM pg_user WHERE usesysid =
'relowner';

where 'relowner' is the relowner value returned from the first
SELECT statement. If you don't get any rows, then the owner of
the table has been deleted.

>
> I have also tried to createdb "test1" after destroying
> it once, but I get "Sorry, test1 already exists", even
> though I deleted it from "data/base/"

This sounds to me like the database name still exsist in
pg_database. You should have dropped the database using psql
connected to template1:

$ psql template1

template1=> drop database test1;
DESTROYDB

As a general rule, you should never be mingling with the files on
the filesystem.

>
> Any ideas on how to fix this? I am running 6.5.3 (the
> last one before 7.0) on RH 6.0. I have also run out
> disc space before this and had to destroy some
> databases and init them again, if that is leading to
> this weird behavior.

Running out of disk space could very well be the cause. If you
don't need the data, I would (as root, of course) uninstall the
RPMS, delete any directory that rpm complains is not empty
(/var/lib/pgsql), and reinstall the rpms. Then startup postgres
from /etc/rc.d with:

/etc/rc.d/init.d/postgres start

which will then run initdb and properly reconstruct a new
database system. If you do need the data and the owner of the
tables no longer exists, you could try and readd the owner using
CREATE USER. The usesysid in pg_user should match the relowner
attribute in pg_class. pg_user is a view on pg_shadow. Then you
might be able to successfully use pg_dump to dump the database.
Otherwise, you might be stuck with using COPY to copy the data to
a text file. You could also try pg_upgrade, but I haven't used
it.

>
> Thanks in advance

Hope that helps,

Mike Mascari