RE: Unable to create tables...

>
> ------------------------------------------------------------------------
>
> Unable to create tables...
>
> ------------------------------------------------------------------------
>
> * From: Don Baccus <dhogaza(at)pacifier(dot)com>
> * To: Postgres Hackers List <hackers(at)postgreSQL(dot)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(at)pacifier(dot)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(at)alert(dot)infoplease(dot)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