Thread: More information
Okee - more information on my problem.
Postgresql version 6.3.2 on redhat linux 5.2
Here is a cut&paste from what happens
************************
$ psql mydb
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: mydb
mydb=>\d
Database = mydb
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| myuid | beep | table |
| myuid | history | table |
| myuid | history_seq | sequence |
| myuid | historyb | table |
| myuid | other | table |
| myuid | record | table |
| myuid | recordb | table |
+------------------+----------------------------------+----------+
mydb=> select * from history_seq;
sequence_name|last_value|increment_by|max_value|min_value|cache_value|is_cycled|is_called
-------------+----------+------------+---------+---------+-----------+---------+---------
(0 rows)
mydb=> drop sequence history_seq;
ERROR: DeletePgTypeTuple: history_seq type nonexistent
mydb=>
************************
I have no idea what to do about this, cos I need to update my database
but I cant do it with this happening, cos when I dump the database I
get
************************
$ pg_dump mydb > dump.out
dumpSequence(history_seq): 0 (!= 1) tuples returned by SELECT
************************
I need some solution to this if anyone knows. Surely a database this
complex HAS to have some sanity-checking routines and database
restoration. The documentation helpfully says that this section is
missing, however. I have based a LARGE project on this, and so far, I
cant start this project till this problem is fixed.
Thanx
M Simms
Try the vacuum command and see if that cleans up the database. Then, if that fails, instead of dumping the entire db, use the "pg_dump -t table dbname > dbname.table.out" to dump each table that is still in good shape. Sorry I can't help you with what the root cause is, but the above may get you out of the jam. Thomas M Simms wrote: > > Okee - more information on my problem. > > Postgresql version 6.3.2 on redhat linux 5.2 > > Here is a cut&paste from what happens > ************************ > $ psql mydb > Welcome to the POSTGRESQL interactive sql monitor: > Please read the file COPYRIGHT for copyright terms of POSTGRESQL > > type \? for help on slash commands > type \q to quit > type \g or terminate with semicolon to execute query > You are currently connected to the database: mydb > > mydb=>\d > > Database = mydb > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | myuid | beep | table | > | myuid | history | table | > | myuid | history_seq | sequence | > | myuid | historyb | table | > | myuid | other | table | > | myuid | record | table | > | myuid | recordb | table | > +------------------+----------------------------------+----------+ > mydb=> select * from history_seq; > sequence_name|last_value|increment_by|max_value|min_value|cache_value|is_cycled|is_called > -------------+----------+------------+---------+---------+-----------+---------+--------- > (0 rows) > mydb=> drop sequence history_seq; > ERROR: DeletePgTypeTuple: history_seq type nonexistent > mydb=> > ************************ > > I have no idea what to do about this, cos I need to update my database > but I cant do it with this happening, cos when I dump the database I > get > > ************************ > > $ pg_dump mydb > dump.out > dumpSequence(history_seq): 0 (!= 1) tuples returned by SELECT > > ************************ > > I need some solution to this if anyone knows. Surely a database this > complex HAS to have some sanity-checking routines and database > restoration. The documentation helpfully says that this section is > missing, however. I have based a LARGE project on this, and so far, I > cant start this project till this problem is fixed. > > Thanx > > M Simms
Thanks for the response. However, this is only part of the problem, and this
will not fix it.
I have done a vacuum, which seems to succede, but does not fix the error.
If I destroy the database, and recreate it, I get amcreate: relationship
already exists *as I create the tables for the first time in the
database* The database and tables are of the same name as the old
ones, of course, but the old ones *should be gone* but they arent.
Example:
*************************
$destroydb mydb
$destroydb mydb
ERROR: destroydb: database mydb does not exist.
destroydb: database destroy failed on mydb.
$createdb mydb
$echo create table beep \( test int \)\; | psql mydb
create table beep ( test int );
ERROR: amcreate: beep relation already exists
EOF
*************************
This means no matter what i do, I cannot even use the same database
name again!
Any further ideas would be *greatly* appreciated. Surely there has to
be something like a fsck for the database where it just does an
integrity check and asks you if something is an error, should it be
removed/fixed... ?
Thanks again
M Simms
>
> Try the vacuum command and see if that cleans up the database.
> Then, if that fails, instead of dumping the entire db, use
> the "pg_dump -t table dbname > dbname.table.out" to dump
> each table that is still in good shape. Sorry I can't help
> you with what the root cause is, but the above may get you
> out of the jam.
>
> Thomas
>
> M Simms wrote:
> >
> > Okee - more information on my problem.
> >
> > Postgresql version 6.3.2 on redhat linux 5.2
> >
> > Here is a cut&paste from what happens
> > ************************
> > $ psql mydb
> > Welcome to the POSTGRESQL interactive sql monitor:
> > Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> >
> > type \? for help on slash commands
> > type \q to quit
> > type \g or terminate with semicolon to execute query
> > You are currently connected to the database: mydb
> >
> > mydb=>\d
> >
> > Database = mydb
> > +------------------+----------------------------------+----------+
> > | Owner | Relation | Type |
> > +------------------+----------------------------------+----------+
> > | myuid | beep | table |
> > | myuid | history | table |
> > | myuid | history_seq | sequence |
> > | myuid | historyb | table |
> > | myuid | other | table |
> > | myuid | record | table |
> > | myuid | recordb | table |
> > +------------------+----------------------------------+----------+
> > mydb=> select * from history_seq;
> > sequence_name|last_value|increment_by|max_value|min_value|cache_value|is_cycled|is_called
> > -------------+----------+------------+---------+---------+-----------+---------+---------
> > (0 rows)
> > mydb=> drop sequence history_seq;
> > ERROR: DeletePgTypeTuple: history_seq type nonexistent
> > mydb=>
> > ************************
> >
> > I have no idea what to do about this, cos I need to update my database
> > but I cant do it with this happening, cos when I dump the database I
> > get
> >
> > ************************
> >
> > $ pg_dump mydb > dump.out
> > dumpSequence(history_seq): 0 (!= 1) tuples returned by SELECT
> >
> > ************************
> >
> > I need some solution to this if anyone knows. Surely a database this
> > complex HAS to have some sanity-checking routines and database
> > restoration. The documentation helpfully says that this section is
> > missing, however. I have based a LARGE project on this, and so far, I
> > cant start this project till this problem is fixed.
> >
> > Thanx
> >
> > M Simms
>
I've seen this behavior in 6.3.2 also. It went away in 6.4. Steve On Thu, 11 Mar 1999, M Simms wrote: > Thanks for the response. However, this is only part of the problem, and this > will not fix it. > > I have done a vacuum, which seems to succede, but does not fix the error. > > If I destroy the database, and recreate it, I get amcreate: relationship > already exists *as I create the tables for the first time in the > database* The database and tables are of the same name as the old > ones, of course, but the old ones *should be gone* but they arent. > > Example: > ************************* > $destroydb mydb > $destroydb mydb > ERROR: destroydb: database mydb does not exist. > destroydb: database destroy failed on mydb. > $createdb mydb > $echo create table beep \( test int \)\; | psql mydb > create table beep ( test int ); > ERROR: amcreate: beep relation already exists > EOF > ************************* > > This means no matter what i do, I cannot even use the same database > name again! > > Any further ideas would be *greatly* appreciated. Surely there has to > be something like a fsck for the database where it just does an > integrity check and asks you if something is an error, should it be > removed/fixed... ? > > Thanks again > > M Simms > > > > > > Try the vacuum command and see if that cleans up the database. > > Then, if that fails, instead of dumping the entire db, use > > the "pg_dump -t table dbname > dbname.table.out" to dump > > each table that is still in good shape. Sorry I can't help > > you with what the root cause is, but the above may get you > > out of the jam. > > > > Thomas > > > > M Simms wrote: > > > > > > Okee - more information on my problem. > > > > > > Postgresql version 6.3.2 on redhat linux 5.2 > > > > > > Here is a cut&paste from what happens > > > ************************ > > > $ psql mydb > > > Welcome to the POSTGRESQL interactive sql monitor: > > > Please read the file COPYRIGHT for copyright terms of POSTGRESQL > > > > > > type \? for help on slash commands > > > type \q to quit > > > type \g or terminate with semicolon to execute query > > > You are currently connected to the database: mydb > > > > > > mydb=>\d > > > > > > Database = mydb > > > +------------------+----------------------------------+----------+ > > > | Owner | Relation | Type | > > > +------------------+----------------------------------+----------+ > > > | myuid | beep | table | > > > | myuid | history | table | > > > | myuid | history_seq | sequence | > > > | myuid | historyb | table | > > > | myuid | other | table | > > > | myuid | record | table | > > > | myuid | recordb | table | > > > +------------------+----------------------------------+----------+ > > > mydb=> select * from history_seq; > > > sequence_name|last_value|increment_by|max_value|min_value|cache_value|is_cycled|is_called > > > -------------+----------+------------+---------+---------+-----------+---------+--------- > > > (0 rows) > > > mydb=> drop sequence history_seq; > > > ERROR: DeletePgTypeTuple: history_seq type nonexistent > > > mydb=> > > > ************************ > > > > > > I have no idea what to do about this, cos I need to update my database > > > but I cant do it with this happening, cos when I dump the database I > > > get > > > > > > ************************ > > > > > > $ pg_dump mydb > dump.out > > > dumpSequence(history_seq): 0 (!= 1) tuples returned by SELECT > > > > > > ************************ > > > > > > I need some solution to this if anyone knows. Surely a database this > > > complex HAS to have some sanity-checking routines and database > > > restoration. The documentation helpfully says that this section is > > > missing, however. I have based a LARGE project on this, and so far, I > > > cant start this project till this problem is fixed. > > > > > > Thanx > > > > > > M Simms > > > > >