pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedUnable to create tables...
8+ messages / 3 participants
[nested] [flat]
* Unable to create tables...
@ 1999-08-06 16:42 Don Baccus <dhogaza@pacifier.com>
0 siblings, 3 replies; 8+ messages in thread
From: Don Baccus @ 1999-08-06 16:42 UTC (permalink / raw)
To: Postgres Hackers List <hackers@postgreSQL.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 18:14 Lamar Owen <lamar.owen@wgcr.org>
parent: Don Baccus <dhogaza@pacifier.com>
2 siblings, 0 replies; 8+ messages in thread
From: Lamar Owen @ 1999-08-06 18:14 UTC (permalink / raw)
To: Don Baccus <dhogaza@pacifier.com>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
Don Baccus wrote:
> I can't "drop table foo", getting an error message telling
> me the class doesn't exist, so that's not the problem.
> It would seem as some system table is being corrupted???
Check pg_tables and pg_class -- select * from pg_tables; will give you a
list of tables from the system catalog, and select * from ps_class; will
do the same for classes -- if the psuedo-dropped table shows in either
of these tables, delete it, and see if that helps. If this indeed is a
system table corruption..... eeewwww....
Lamar Owen
WGCR Internet Radio
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 18:23 Don Baccus <dhogaza@pacifier.com>
parent: Don Baccus <dhogaza@pacifier.com>
2 siblings, 0 replies; 8+ messages in thread
From: Don Baccus @ 1999-08-06 18:23 UTC (permalink / raw)
To: Lamar Owen <lamar.owen@wgcr.org>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
At 02:14 PM 8/6/99 -0400, Lamar Owen wrote:
>Don Baccus wrote:
>
>> I can't "drop table foo", getting an error message telling
>> me the class doesn't exist, so that's not the problem.
>
>> It would seem as some system table is being corrupted???
>
>Check pg_tables and pg_class -- select * from pg_tables; will give you a
>list of tables from the system catalog, and select * from ps_class; will
>do the same for classes -- if the psuedo-dropped table shows in either
>of these tables, delete it, and see if that helps. If this indeed is a
>system table corruption..... eeewwww....
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 :(
- 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 18:54 Don Baccus <dhogaza@pacifier.com>
parent: Don Baccus <dhogaza@pacifier.com>
2 siblings, 4 replies; 8+ messages in thread
From: Don Baccus @ 1999-08-06 18:54 UTC (permalink / raw)
To: Lamar Owen <lamar.owen@wgcr.org>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
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?
- 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 19:46 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Don Baccus <dhogaza@pacifier.com>
3 siblings, 0 replies; 8+ messages in thread
From: Ross J. Reedstrom @ 1999-08-06 19:46 UTC (permalink / raw)
To: Don Baccus <dhogaza@pacifier.com>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 19:53 Lamar Owen <lamar.owen@wgcr.org>
parent: Don Baccus <dhogaza@pacifier.com>
3 siblings, 0 replies; 8+ messages in thread
From: Lamar Owen @ 1999-08-06 19:53 UTC (permalink / raw)
To: Don Baccus <dhogaza@pacifier.com>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
Don Baccus wrote:
> Obviously, some table contents somewhere must be messed
> up. Any ideas?
Ooooo....
If this were happening to me, I'd probably stop postmaster, rename the
PGDATA tree to something else, initdb, start postmaster, restore from
the last good dump, stop postmaster, copy back the user database dirs
under PGDATA, restart postmaster, VACUUM all tables -- on the production
machine, if that is where the problems are. Then, I'd pull that PGDATA
binary backup over to a development workstation, start up a postmaster
pointing to it, and do a post-mortem, checking all system tables for
their contents, running vacuum, et al (all the while keeping a good copy
of the old PGDATA tree -- just in case something blows up).
Obviously, some system catalog somewhere is getting farkled -- Don, I'm
assuming that you are vacuuming often.
HTHaL
Lamar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 19:59 Don Baccus <dhogaza@pacifier.com>
parent: Don Baccus <dhogaza@pacifier.com>
3 siblings, 0 replies; 8+ messages in thread
From: Don Baccus @ 1999-08-06 19:59 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
At 02:46 PM 8/6/99 -0500, Ross J. Reedstrom wrote:
>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.
Thanks, I thought of this one myself, and deleted "foo". This is
when it got into the mode of allowing "create table foo..." and
an apparently successful "drop table foo", but with "foo" left
behind in pg_class (I think that's right) and "select count(*)
from foo" returning 0 rows (i.e. the relation really seems to
exist!)
>
>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)
Yes, this isn't my problem, though.
- 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [HACKERS] Unable to create tables...
@ 1999-08-06 20:00 Don Baccus <dhogaza@pacifier.com>
parent: Don Baccus <dhogaza@pacifier.com>
3 siblings, 0 replies; 8+ messages in thread
From: Don Baccus @ 1999-08-06 20:00 UTC (permalink / raw)
To: Lamar Owen <lamar.owen@wgcr.org>; +Cc: Postgres Hackers List <hackers@postgreSQL.org>
At 03:53 PM 8/6/99 -0400, Lamar Owen wrote:
>Don Baccus wrote:
>> Obviously, some table contents somewhere must be messed
>> up. Any ideas?
>
>Ooooo....
>
>If this were happening to me, I'd probably stop postmaster, rename the
>PGDATA tree to something else, initdb, start postmaster, restore from
>the last good dump, stop postmaster, copy back the user database dirs
>under PGDATA, restart postmaster, VACUUM all tables -- on the production
>machine, if that is where the problems are. Then, I'd pull that PGDATA
>binary backup over to a development workstation, start up a postmaster
>pointing to it, and do a post-mortem, checking all system tables for
>their contents, running vacuum, et al (all the while keeping a good copy
>of the old PGDATA tree -- just in case something blows up).
I decided awhile back to punt and rebuild.
>Obviously, some system catalog somewhere is getting farkled -- Don, I'm
>assuming that you are vacuuming often.
Nightly, after dumping.
- 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1999-08-06 20:00 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-06 16:42 Unable to create tables... Don Baccus <dhogaza@pacifier.com>
1999-08-06 18:14 ` Lamar Owen <lamar.owen@wgcr.org>
1999-08-06 18:23 ` Don Baccus <dhogaza@pacifier.com>
1999-08-06 18:54 ` Don Baccus <dhogaza@pacifier.com>
1999-08-06 19:46 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-06 19:53 ` Lamar Owen <lamar.owen@wgcr.org>
1999-08-06 19:59 ` Don Baccus <dhogaza@pacifier.com>
1999-08-06 20:00 ` Don Baccus <dhogaza@pacifier.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox