Date: Fri, 6 Aug 1999 14:46:38 -0500
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: Don Baccus <dhogaza@pacifier.com>
Cc: Postgres Hackers List <hackers@postgreSQL.org>
Subject: Re: [HACKERS] Unable to create tables...
Message-ID: <19990806144638.A17288@wallace.ece.rice.edu>
References: <37AB25F0.CB67F617@wgcr.org>
 <37AAFF29.958CE9F2@alumni.caltech.edu>
	<3.0.1.32.19990806094228.00e0388c@mail.pacifier.com>
	<3.0.1.32.19990806112347.00e0d9b4@mail.pacifier.com>
	<3.0.1.32.19990806115431.00e087c8@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
In-Reply-To: <3.0.1.32.19990806115431.00e087c8@mail.pacifier.com>;
	from Don Baccus on Fri, Aug 06, 1999 at 11:54:31AM -0700

On Fri, Aug 06, 1999 at 11:54:31AM -0700, Don Baccus wrote:
> At 11:23 AM 8/6/99 -0700, Don Baccus wrote:
> 
> >Neither "pgdump_oid" or "foo" (my other example from my
> >follow-up message) appear to exist in pg_class or pg_tables.
> >
> >Thanks for the suggestion, though :(
> 
> Things are getting more and more odd...
> 
> I've done some more testing and things are now in a state
> where I can create a table, drop the table (and get the
> message "DROP" back), yet the relation still exists.
> 
> In fact, I can do a "select count(*) from ..." on it and
> get zero rows back.
> 
> Argh!
> 
> Of course, now if I try to create a table with that name,
> I'm told the relation already exists.
> 
> Different than the situation with pgdump_oid and foo,
> where I'm just told that the create failed.
> 
> Obviously, some table contents somewhere must be messed
> up.  Any ideas?
> 

Check to see if there are files in the pgsql/data/base/'yourdbname'
directory called 'pgdump_oid' and 'foo'. Some situations lead to a table
being almost completely deleted, but leaving the file behind. Doesn't
explain the 'table still there' phenomena, but might let you recreate a
'dropped' table.

Ross
P.S. once common problem is dropping a table doesn't always get all the
objects created by 'convenience' types. For example, not not sure the 
sequence created for a serial type gets dropped with its table. In fact,
I'm pretty sure it doesn't (and, for now, shouldn't)

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005