Re: [HACKERS] Unable to create tables...

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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005