Обсуждение: ERROR: could not open relation with OID 2836
PgSQL is returning that error when I open pgdmin and when I run some queries related to pg_buffercache. Also pg_dump cannot dump the DB. PgSQL version is 8.3.3 and happened one day after loading the DB there. Anything that can be done? or I have to restore a backup and put current data again? Thanks Rodrigo Gonzalez
Rodrigo Gonzalez wrote: > PgSQL is returning that error when I open pgdmin and when I run some > queries related to pg_buffercache. Also pg_dump cannot dump the DB. > > PgSQL version is 8.3.3 and happened one day after loading the DB there. What platform are you using? If Windows: - Which version of Windows? - do you have a virus scanner on the system? Which one? Version? Otherwise: - What OS and version are you using? - What filesystem type is the database on? - Are you using any sort of RAID / LVM? If so, what kind? -- Craig Ringer
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> PgSQL is returning that error when I open pgdmin and when I run some
> queries related to pg_buffercache. Also pg_dump cannot dump the DB.
> PgSQL version is 8.3.3 and happened one day after loading the DB there.
That raises a lot of questions about the trustworthiness of the platform
you're using (hardware, kernel, etc). In 8.3, 2836 is pg_proc's TOAST
table and should certainly never get deleted. I think you have
filesystem or hardware issues.
regards, tom lane
Craig Ringer wrote: > Rodrigo Gonzalez wrote: >> PgSQL is returning that error when I open pgdmin and when I run some >> queries related to pg_buffercache. Also pg_dump cannot dump the DB. >> >> PgSQL version is 8.3.3 and happened one day after loading the DB there. > > What platform are you using? > > If Windows: > - Which version of Windows? > - do you have a virus scanner on the system? Which one? Version? > > Otherwise: > - What OS and version are you using? > - What filesystem type is the database on? > - Are you using any sort of RAID / LVM? If so, what kind? > > -- > Craig Ringer It's running under CentOS 4.4 using ext3, no RAID or LVM. Server is quad xeon 64 bits 3 GHz
Tom Lane wrote: > Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >> PgSQL is returning that error when I open pgdmin and when I run some >> queries related to pg_buffercache. Also pg_dump cannot dump the DB. >> PgSQL version is 8.3.3 and happened one day after loading the DB there. > > That raises a lot of questions about the trustworthiness of the platform > you're using (hardware, kernel, etc). In 8.3, 2836 is pg_proc's TOAST > table and should certainly never get deleted. I think you have > filesystem or hardware issues. > > regards, tom lane Can be related to oom killer? I noticed today that it was invoked twice. I am making other server to move the DB finally there, but would want to know if the problem is hardware or not to be prepared for future... Best regards Rodrigo Gonzalez
Rodrigo Gonzalez <rjgonzale@gmail.com> writes: > Craig Ringer wrote: >> What platform are you using? > It's running under CentOS 4.4 using ext3, no RAID or LVM. > Server is quad xeon 64 bits 3 GHz Ugh, I'd have liked to think RHEL4/Centos4 would be more reliable than that :-(. Still, you might have an issue with trying to use hardware that's not supported by RHEL4, especially if it's not a very recent version of RHEL4. Did you check compatibility charts before purchasing? https://hardware.redhat.com/ regards, tom lane
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> It had been working with pgsql 8.1 and 8.2 for 2 years without problems.
> Suspicious is that problems started next day I've upgraded to 8.3.
Did you update anything else at the same time?
regards, tom lane
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> Tom Lane wrote:
>> Did you update anything else at the same time?
> No, just postgres was updated
Well, that does start to sound like it could be a PG bug; but no one
else is reporting anything like it. Can you put together a
self-contained test case?
regards, tom lane
Tom Lane wrote: > Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >> It had been working with pgsql 8.1 and 8.2 for 2 years without problems. >> Suspicious is that problems started next day I've upgraded to 8.3. > > Did you update anything else at the same time? > > regards, tom lane No, just postgres was updated
Tom Lane wrote: > Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >> Tom Lane wrote: >>> Did you update anything else at the same time? > >> No, just postgres was updated > > Well, that does start to sound like it could be a PG bug; but no one > else is reporting anything like it. Can you put together a > self-contained test case? > > regards, tom lane No idea what to do.... Basically I should reinstall again PG with the same configuration and wait 1 night. Any log you need or want? anything to do besides doing the same I did? Thanks Rodrigo Gonzalez
Tom Lane wrote: > Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >> Craig Ringer wrote: >>> What platform are you using? > >> It's running under CentOS 4.4 using ext3, no RAID or LVM. >> Server is quad xeon 64 bits 3 GHz > > Ugh, I'd have liked to think RHEL4/Centos4 would be more reliable than > that :-(. Still, you might have an issue with trying to use hardware > that's not supported by RHEL4, especially if it's not a very recent > version of RHEL4. Did you check compatibility charts before purchasing? > https://hardware.redhat.com/ > > regards, tom lane It had been working with pgsql 8.1 and 8.2 for 2 years without problems. Suspicious is that problems started next day I've upgraded to 8.3. I've tried reinstalling 8.3 from scratch and again, next morning, oid 2836 is missing... Before you ask, nothing "strange" is running at nights, just a pg_dump from other machine to make a backup.... Best regards Rodrigo Gonzalez
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> Basically I should reinstall again PG with the same configuration and
> wait 1 night. Any log you need or want? anything to do besides doing the
> same I did?
Umm ... if I reinstall PG and wait one night, I'm quite sure that
nothing much will happen. You need to show me how to reproduce the
problem you're having.
regards, tom lane
Tom Lane wrote: > Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >> Basically I should reinstall again PG with the same configuration and >> wait 1 night. Any log you need or want? anything to do besides doing the >> same I did? > > Umm ... if I reinstall PG and wait one night, I'm quite sure that > nothing much will happen. You need to show me how to reproduce the > problem you're having. > > regards, tom lane Dont know exactly what you mean, if you are talking about the moment that I receive the error... # pg_dump -U postgres db pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not open relation with OID 2836 pg_dump: The command was: SELECT proretset, prosrc, probin, proallargtypes, proargmodes, proargnames, provolatile, proisstrict, prosecdef, proconfig, procost, prorows, (SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname FROM pg_catalog.pg_proc WHERE oid = '17018'::pg_catalog.oid Anything else I can do or give you, just tell me and I will try...
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> Dont know exactly what you mean, if you are talking about the moment
> that I receive the error...
No, it's clear that things are already broken before pg_dump started.
You need to show us how to get to this state from a fresh database.
regards, tom lane
Tom Lane wrote:
> Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
>> Dont know exactly what you mean, if you are talking about the moment
>> that I receive the error...
>
> No, it's clear that things are already broken before pg_dump started.
> You need to show us how to get to this state from a fresh database.
>
> regards, tom lane
Interesting....a new problem maybe, or maybe the same one....
Steps done:
# rpm -Uvh postgresql-8.3.3-1PGDG.rhel4.x86_64.rpm
postgresql-contrib-8.3.3-1PGDG.rhel4.x86_64.rpm
postgresql-devel-8.3.3-1PGDG.rhel4.x86_64.rpm
postgresql-libs-8.3.3-1PGDG.rhel4.x86_64.rpm
postgresql-plperl-8.3.3-1PGDG.rhel4.x86_64.rpm
postgresql-server-8.3.3-1PGDG.rhel4.x86_64.rpm
warning: postgresql-8.3.3-1PGDG.rhel4.x86_64.rpm: V3 DSA signature:
NOKEY, key ID 442df0f8
Preparing... ###########################################
[100%]
1:postgresql-libs ###########################################
[ 17%]
2:postgresql ###########################################
[ 33%]
3:postgresql-server ###########################################
[ 50%]
4:postgresql-contrib ###########################################
[ 67%]
5:postgresql-devel ###########################################
[ 83%]
6:postgresql-plperl ###########################################
[100%]
#/etc/init.d/postgresql initdb
#cd /var/lib/pgsql/data
# diff -u postgresql.conf /root/backup/pgsql/postgresql.conf
--- postgresql.conf 2008-06-26 01:14:48.000000000 -0500
+++ /root/backup/pgsql/postgresql.conf 2008-06-26 01:07:44.000000000 -0500
@@ -53,7 +53,7 @@
# - Connection Settings -
-#listen_addresses = 'localhost' # what IP address(es) to listen on;
+listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
@@ -73,7 +73,7 @@
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
-#ssl = off # (change requires restart)
+ssl = on # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#password_encryption = on
@@ -104,7 +104,7 @@
# - Memory -
-shared_buffers = 32MB # min 128kB or max_connections*16kB
+shared_buffers = 256MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
#cp /root/backup/pgsql/server.* .
#chown postgres:postgres *
# cp /root/backup/pgsql/postgresql.conf .
# cp /root/backup/pgsql/pg_hba.conf .
#/etc/init.d/postgresql start
##### created the user and the DB
##### restored DB from backup. That DB has pg_buffercache contrib module
(just in case is important) and mysqlcompat too
# psql -U postgres db
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
db=# SELECT c.relname, pg_size_pretty(count(*) * 8192) as buffered,
round(100.0 * count(*) / (select setting from pg_settings where
name='shared_buffers')::integer,1) AS buffers_percent, round(100.0 *
count(*) * 8192 / pg_relation_size(c.relname),1) as percent_of_relation
FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode =
c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND
d.datname = current_database()) GROUP BY c.relname ORDER BY 3 DESC LIMIT 10;
ERROR: relation "pg_toast_1255" does not exist
db=#
On Jun 26, 2008, at 5:41 AM, Rodrigo Gonzalez wrote: > Tom Lane wrote: >> Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >>> Craig Ringer wrote: >>>> What platform are you using? >> >>> It's running under CentOS 4.4 using ext3, no RAID or LVM. >>> Server is quad xeon 64 bits 3 GHz >> >> Ugh, I'd have liked to think RHEL4/Centos4 would be more reliable >> than >> that :-(. Still, you might have an issue with trying to use hardware >> that's not supported by RHEL4, especially if it's not a very recent >> version of RHEL4. Did you check compatibility charts before >> purchasing? >> https://hardware.redhat.com/ >> >> regards, tom lane > > It had been working with pgsql 8.1 and 8.2 for 2 years without > problems. > Suspicious is that problems started next day I've upgraded to 8.3. > > I've tried reinstalling 8.3 from scratch and again, next morning, oid > 2836 is missing... Ok, throwing a few "random" questions in your direction: What procedure did you use to do those upgrades? Maybe something went wrong there? I'm assuming you upgraded using dump/restore, or postgres would have complained about the version of the data files at startup, but maybe you did something unusual. Are you sure there's only one version of postgres running? Are all your libraries up to date, no old versions hanging around where they should have been replaced? Do you have any stored procedures in C? If so, do you perhaps use malloc/free instead of the ones Postgres provides (reasoning you may be freeing a reference to the toast table somehow)? Is that data-file on a mirror where one part of the mirror may be mirroring a bad sector over the good one on the other drive(s)? I may be talking nonsense, I'm no Tom Lane, but I know a fair share about postgres ;) Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4863ce3b243481261463208!
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> Tom Lane wrote:
>> No, it's clear that things are already broken before pg_dump started.
>> You need to show us how to get to this state from a fresh database.
> Interesting....a new problem maybe, or maybe the same one....
> ...
> ERROR: relation "pg_toast_1255" does not exist
Looks like almost the same problem: pg_proc's TOAST table is missing.
But this behavior implies that the pg_class row for it is missing,
whereas the other error suggested that the system catalog entries were
fine but the on-disk file was missing. Odd.
When you say "restored from backup", are you talking about a pg_dump
backup, or what?
regards, tom lane
Hi, I have problem with my DB: snort=# vacuum full; WARNING: index "ip_src_idx" contains 1921678 row versions, but table contains 1921693 row versions HINT: Rebuild the index with REINDEX. WARNING: index "ip_dst_idx" contains 1921668 row versions, but table contains 1921693 row versions HINT: Rebuild the index with REINDEX. ERROR: could not read block 988 of relation 1663/16384/16472: Input/output error snort=# reindex index ip_src_idx; ERROR: concurrent insert in progress How to solve this problem? Is there any other method fixing the index? thanks in advance, Ganbold
Ganbold <ganbold@micom.mng.net> writes:
> I have problem with my DB:
> ERROR: could not read block 988 of relation 1663/16384/16472:
> Input/output error
> How to solve this problem?
First, replace your failed disk; then get out your backups and restore
your system ...
regards, tom lane
Tom Lane wrote: > Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >> Tom Lane wrote: >>> No, it's clear that things are already broken before pg_dump started. >>> You need to show us how to get to this state from a fresh database. > >> Interesting....a new problem maybe, or maybe the same one.... >> ... >> ERROR: relation "pg_toast_1255" does not exist > > Looks like almost the same problem: pg_proc's TOAST table is missing. > But this behavior implies that the pg_class row for it is missing, > whereas the other error suggested that the system catalog entries were > fine but the on-disk file was missing. Odd. > > When you say "restored from backup", are you talking about a pg_dump > backup, or what? > > regards, tom lane yes, a pg_dump backup.
Alban Hertroys wrote: > On Jun 26, 2008, at 5:41 AM, Rodrigo Gonzalez wrote: > >> Tom Lane wrote: >>> Rodrigo Gonzalez <rjgonzale@gmail.com> writes: >>>> Craig Ringer wrote: >>>>> What platform are you using? >>> >>>> It's running under CentOS 4.4 using ext3, no RAID or LVM. >>>> Server is quad xeon 64 bits 3 GHz >>> >>> Ugh, I'd have liked to think RHEL4/Centos4 would be more reliable than >>> that :-(. Still, you might have an issue with trying to use hardware >>> that's not supported by RHEL4, especially if it's not a very recent >>> version of RHEL4. Did you check compatibility charts before purchasing? >>> https://hardware.redhat.com/ >>> >>> regards, tom lane >> >> It had been working with pgsql 8.1 and 8.2 for 2 years without problems. >> Suspicious is that problems started next day I've upgraded to 8.3. >> >> I've tried reinstalling 8.3 from scratch and again, next morning, oid >> 2836 is missing... > > Ok, throwing a few "random" questions in your direction: > > What procedure did you use to do those upgrades? Maybe something went > wrong there? I'm assuming you upgraded using dump/restore, or postgres > would have complained about the version of the data files at startup, > but maybe you did something unusual. > > Are you sure there's only one version of postgres running? Yes, just 8.3.3 right now is running. > > Are all your libraries up to date, no old versions hanging around where > they should have been replaced? I have postgresql-libs for 8.3.3 and the compat rpm installed cause of other software that require it. > > Do you have any stored procedures in C? If so, do you perhaps use > malloc/free instead of the ones Postgres provides (reasoning you may be > freeing a reference to the toast table somehow)? No stored procedure in C, just SQL and PlPgSQL store procedures are used. Well, pg_buffercache of course is in C as I know....maybe I should check taking it out and see what happens... > > Is that data-file on a mirror where one part of the mirror may be > mirroring a bad sector over the good one on the other drive(s)? > No, this is a small, simple server with just one disk for OS and one for data. > > I may be talking nonsense, I'm no Tom Lane, but I know a fair share > about postgres ;) Thank you for your help. > Regards, > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:825,4863ce39243482861390956! > >
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> Tom Lane wrote:
>> When you say "restored from backup", are you talking about a pg_dump
>> backup, or what?
> yes, a pg_dump backup.
There must be something mighty odd in that backup. Would you be willing
to send it to me off-list, so I can try to reproduce the problem here?
regards, tom lane
| use REINDEX http://www.postgresql.org/docs/8.3/static/sql-reindex.html tell us the result thank --- On Fri, 6/27/08, Ganbold <ganbold@micom.mng.net> wrote: From: Ganbold <ganbold@micom.mng.net> |
| use REINDEX REINDEX ip_dst_idx OR REINDEX table_name http://www.postgresql.org/docs/8.3/static/sql-reindex.html tell us the result thank --- On Fri, 6/27/08, Ganbold <ganbold@micom.mng.net> wrote: From: Ganbold <ganbold@micom.mng.net> |
Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
> pg_dump is working fine now, the problem appear with the pg_buffercache
> query...without it I dont notice anything wrong with DB....but of course
> there is something wrong. Can be pg_buffercache the problem?
Oh ... looking again at your latest problem query, the query is buggy:
db=# SELECT ... pg_relation_size(c.relname) ...
FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode ...
ERROR: relation "pg_toast_1255" does not exist
The pg_toast schema isn't in your search path so you can't just do
"pg_relation_size(c.relname)". You'd be better off using
pg_relation_size(c.oid) anyway.
I was misled by the chance coincidence that pg_proc's toast table was
the one mentioned, otherwise I'd probably have seen this sooner.
So this is not a bug, and not related to the original problem. We still
don't know what the original problem was, but I wonder if it might have
been of the same ilk. I don't think you ever showed us the exact query
that led to the "could not open relation" message?
regards, tom lane
Tom Lane wrote:
> Rodrigo Gonzalez <rjgonzale@gmail.com> writes:
>> pg_dump is working fine now, the problem appear with the pg_buffercache
>> query...without it I dont notice anything wrong with DB....but of course
>> there is something wrong. Can be pg_buffercache the problem?
>
> Oh ... looking again at your latest problem query, the query is buggy:
>
> db=# SELECT ... pg_relation_size(c.relname) ...
> FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode ...
> ERROR: relation "pg_toast_1255" does not exist
>
> The pg_toast schema isn't in your search path so you can't just do
> "pg_relation_size(c.relname)". You'd be better off using
> pg_relation_size(c.oid) anyway.
>
> I was misled by the chance coincidence that pg_proc's toast table was
> the one mentioned, otherwise I'd probably have seen this sooner.
>
> So this is not a bug, and not related to the original problem. We still
> don't know what the original problem was, but I wonder if it might have
> been of the same ilk. I don't think you ever showed us the exact query
> that led to the "could not open relation" message?
>
> regards, tom lane
Ok, adding pg_toast to search path worked fine. I dont understand why in
my laptop machine with the same search path ("$user",public) worked
without problem.
I will see how to get the problem again, cause this query was the one
that returned problem at oid 2836. So cannot reproduce right now.
Thanks for your time and if I see the error again I will write again.
It shows following when I try to reindex: snort=# reindex index ip_src_idx; ERROR: concurrent insert in progress So I think it might be hard disk problem as Tom suggested. Unfortunately I don't have replacement hard disk now, so I guess I might change the DB location for a while. thanks, Ganbold Lennin Caro wrote: > use REINDEX > > REINDEX ip_dst_idx > > OR > > REINDEX table_name > > > http://www.postgresql.org/docs/8.3/static/sql-reindex.html > > tell us the result > > thank > > > > --- On *Fri, 6/27/08, Ganbold /<ganbold@micom.mng.net>/* wrote: > > From: Ganbold <ganbold@micom.mng.net> > Subject: [GENERAL] ERROR: concurrent insert in progress > To: pgsql-general@postgresql.org > Date: Friday, June 27, 2008, 3:04 AM > > Hi, > > I have problem with my DB: > > snort=# vacuum full; > WARNING: index "ip_src_idx" contains 1921678 row versions, but table > > contains 1921693 row versions > HINT: Rebuild the index with REINDEX. > WARNING: index "ip_dst_idx" contains 1921668 row versions, but > table > > contains 1921693 row versions > HINT: Rebuild the index with REINDEX. > ERROR: could not read block 988 of relation 1663/16384/16472: > Input/output error > snort=# reindex index ip_src_idx; > ERROR: concurrent insert in progress > > > How to solve this problem? Is there any other method fixing the index? > > thanks in advance, > > Ganbold > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >