Thread: Null records in pg_operator
hello list,
i am facing problem while dumping a database. Here the error i am getting
# /usr/local/pgsql/bin/pg_dump -U postgres ihm > ihm.sql
pg_dump: schema with OID 0 does not exist
#
i also tried dumping data-only but same error.
After googlging about the issue and checking the pg_catalogs i found
that pg_operator is having two null records
# /usr/local/pgsql/bin/psql -U postgres -d ihm
ihm=# select * from pg_operator where oprnamespace is null;
oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft |
oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop |
oprltcmpop | oprgtcmpop | oprcode | oprrest | oprjoin
---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------+---------+---------
| | | | | |
| | | | | |
| | | |
| | | | | |
| | | | | |
| | | |
(2 rows)
ihm=#
i think this is the cause of the problem or is it normal to have null
rows in pg_operator. I tired deleting them but when i try to do so the
connection is terminated.
ihm=# delete from pg_operator where oprnamespace is null;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
i also tried removing these rows by starting postgres in single user mode.
Am i correct in my approach to the problem or i am headed in wrong direction ?
If i am correct how can i delete these null records, i dont have any
idea how they get in there because table structure of pg_operator says
that all the fields are not null.
postgres version 8.1.0
please let me know if more information is required.
thanks
Sandeep
"Sandeep Agarwal" <sandeepagarwal.1980@gmail.com> writes:
> After googlging about the issue and checking the pg_catalogs i found
> that pg_operator is having two null records
> # /usr/local/pgsql/bin/psql -U postgres -d ihm
> ihm=# select * from pg_operator where oprnamespace is null;
> oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft |
> oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop |
> oprltcmpop | oprgtcmpop | oprcode | oprrest | oprjoin
>
---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------+---------+---------
> | | | | | |
> | | | | | |
> | | | |
> | | | | | |
> | | | | | |
> | | | |
> (2 rows)
You've got a badly corrupted table there :-( pg_filedump might yield
some clues as to what happened, but what you'll probably ultimately
have to do is try to copy the pg_operator data over from an undamaged
database in the same cluster. I'd suggest a dump and reload as soon
as you can get a clean pg_dump --- with something like this, I always
wonder what else has been tromped on.
As far as preventing it from happening again:
* are you on the latest minor release for your version of Postgres?
(This doesn't look like any bug I know of, but it's still good advice)
* are you running an up-to-date kernel?
* try running memory and disk diagnostics to see if you've got
flaky hardware.
If I had to bet with no more info, I'd bet on kernel bugs first and
flaky RAM second.
regards, tom lane
On 9/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Sandeep Agarwal" <sandeepagarwal.1980@gmail.com> writes: > > After googlging about the issue and checking the pg_catalogs i found > > that pg_operator is having two null records > > > # /usr/local/pgsql/bin/psql -U postgres -d ihm > > ihm=# select * from pg_operator where oprnamespace is null; > > oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft | > > oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | > > oprltcmpop | oprgtcmpop | oprcode | oprrest | oprjoin > > ---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------+---------+--------- > > | | | | | | > > | | | | | | > > | | | | > > | | | | | | > > | | | | | | > > | | | | > > (2 rows) > > You've got a badly corrupted table there :-( pg_filedump might yield > some clues as to what happened, but what you'll probably ultimately > have to do is try to copy the pg_operator data over from an undamaged > database in the same cluster. I'd suggest a dump and reload as soon > as you can get a clean pg_dump --- with something like this, I always > wonder what else has been tromped on. how can i copy contents from pg_operator of some other db on to this in the same cluster. I tried deleting all records which is giving error and i dont know any way to drop a system catalog table and recreate it. I am not a db guy, can someone please guide me how to use pg_filedump for this specific db, i.e. how to look that what files in the data directory are ment for the corrupted table/database. > > As far as preventing it from happening again: > * are you on the latest minor release for your version of Postgres? > (This doesn't look like any bug I know of, but it's still good advice) > * are you running an up-to-date kernel? > * try running memory and disk diagnostics to see if you've got > flaky hardware. > > If I had to bet with no more info, I'd bet on kernel bugs first and > flaky RAM second. I am on kernel version 2.4, Redhat 8.0, are there some known issues with this ? > regards, tom lane > thanks Sandeep
"Sandeep Agarwal" <sandeepagarwal.1980@gmail.com> writes:
> On 9/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You've got a badly corrupted table there :-( pg_filedump might yield
>> some clues as to what happened, but what you'll probably ultimately
>> have to do is try to copy the pg_operator data over from an undamaged
>> database in the same cluster. I'd suggest a dump and reload as soon
>> as you can get a clean pg_dump --- with something like this, I always
>> wonder what else has been tromped on.
> how can i copy contents from pg_operator of some other db on to this
> in the same cluster. I tried deleting all records which is giving
> error and i dont know any way to drop a system catalog table and
> recreate it.
I was thinking of stopping the postmaster and "cp"ing the file holding
that table. See the PG docs chapter on database physical storage to
figure out which file it is.
>> If I had to bet with no more info, I'd bet on kernel bugs first and
>> flaky RAM second.
> I am on kernel version 2.4, Redhat 8.0, are there some known issues with this ?
Oh dear ... you are *very* badly in need of an OS update.
regards, tom lane