RE: Unable to create tables...

> 
>   ------------------------------------------------------------------------
> 
> Unable to create tables...
> 
>   ------------------------------------------------------------------------
> 
>    * From: Don Baccus <dhogaza@pacifier.com>
>    * To: Postgres Hackers List <hackers@postgreSQL.org>
>    * Subject: Unable to create tables...
>    * Date: Fri, 06 Aug 1999 09:42:28 -0700
> 
>   ------------------------------------------------------------------------
> 
> I mentioned this earlier in the context of pg_dump, which fails
> trying to create the table "pgdump_oid".
> 
> After a bit, a memory jog reminded me that I've seen this
> before, with the table "foo", which I once used for testing.
> 
> After a fair number of "create/drop" cycles, making then
> dropping tables for testing, pgsql now refuses to let me
> "create table foo...", giving the same simple error message
> "can't create foo" as pg_dump's getting on pgdump_oid.
> 
> I can't "drop table foo", getting an error message telling
> me the class doesn't exist, so that's not the problem.
> 
> I CAN create/drop other tables, i.e. "create table bar..."
> followed by "drop table bar" works fine.
> 
> So it doesn't appear to be a general permissions problem,
> i.e. it's not as though the system thinks I don't have
> create table rights.
> 
> It would seem as some system table is being corrupted???
> 
> Does this sound at all familiar?
> 
> Unfortunately, I don't know how to reproduce this other
> than create/drop tables until eventually it fails.  As
> I mentioned in my first note, pg_dump has been running
> nightly on this database for weeks, at least, with no
> errors reported.  Suddenly - poof! can't create pgdump_oid.
> 
> - Don Baccus, Portland OR <dhogaza@pacifier.com>
>   Nature photos, on-line guides, Pacific Northwest
>   Rare Bird Alert Service and other goodies at
>   http://donb.photo.net.
> 

Well, this is a very old post, but I may have part of an answer.

I'm using 6.5.3 on linux (redhat 6.0).

I had a similar or identical problem.  I could not dump a table with
oids.  The error message was something like "cannot create table -
relation pgdump_oid already exists.

But looking at the system table showed that it was nowhere to be found.
And since it couldn't be found, when I tried to drop it, it told me it
did not exist.

So i vacuumed (even though it's done nightly).

I found errors like:

NOTICE:  Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (84) IS
NOT THE SAME AS HEAP' (83)
NOTICE:  Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (84) IS NOT
THE SAME AS HEAP' (83)
VACUUM


There were 4 the first time i vacuumed, but subsequent vacuums showed
only these 2.

So I tried dropping and rebuilding the indexes - can't do that for
system indexes.

I was about to give up and post for help.  But on a lark, I tried to
dump pgdump_oid again.  This time it worked.

Hopefully this is a rare problem.  And I don't know if this will work in
all situations where the above symtoms are found.  But it worked for me,
so I thought I'd post it in case anyone else search the archives for
advice on this sort of problem.

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net