Thread

1. Invisible tables

   Webb Sprague <wsprague100@yahoo.com> — 2000-04-16T17:25:10Z

   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.
   
   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/"
   
   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.
   
   Thanks in advance
   
   __________________________________________________
   Do You Yahoo!?
   Send online invitations with Yahoo! Invites.
   http://invites.yahoo.com
   
   

2. Re: Invisible tables

   Mike Mascari <mascarm@mascari.com> — 2000-04-16T18:17:56Z

   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