Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote:
> Rodrigo Gonzalez <[EMAIL PROTECTED]> writes:
>> pg_dump is working fine now, the problem appear with the pg_buffercache
>> query...without it I dont notice anything wrong with DBbut 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> writes: > pg_dump is working fine now, the problem appear with the pg_buffercache > query...without it I dont notice anything wrong with DBbut 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Alban Hertroys wrote: > On Jun 26, 2008, at 5:41 AM, Rodrigo Gonzalez wrote: > >> Tom Lane wrote: >>> Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 knowmaybe 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! > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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. > >> Interestinga 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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. > Interestinga 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
On Jun 26, 2008, at 5:41 AM, Rodrigo Gonzalez wrote: Tom Lane wrote: Rodrigo Gonzalez <[EMAIL PROTECTED]> 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 Interestinga 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.0 -0500 +++ /root/backup/pgsql/postgresql.conf 2008-06-26 01:07:44.0 -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=# -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Tom Lane wrote: > Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not open relation with OID 2836
Rodrigo Gonzalez <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
