ERROR: could not open relation with OID 2836

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

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:

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

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

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:

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

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.