Обсуждение: ERROR: cache lookup failed for function 125940

Hi folks!

I've a big problem with a database, is a PostgreSQL 9.6 version on Ubuntu.
When a tried read some tables (approximately 7 of 1073) show this error:

*ERROR: cache lookup failed for function 125940*

So, I was reading this like data corruption specially the postgresql's
system catalog . I tried generate a dump but in any case, when I try make a
database dump or table's dump show this error:

*pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
function 406
pg_dump: [archiver (db)] query was: SELECT oid, tableoid, pol.polname,
pol.polcmd, CASE WHEN pol.polroles = '{0}' THEN 'PUBLIC' ELSE
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from
pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual,
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck FROM
pg_catalog.pg_policy pol WHERE polrelid = '129561'*

No matter if this table or table set I can read with a SELECT or not, I
can't run pg_dump in the database show me ever the above error.

I have idea, of almost all tables I can run a copy to send data to a file,
so I will run copy per table, but just 7 tables a can't read by a select.

Mi questions is: Is there any other way to read this tables and extract the
tuples?, Reading data file directly? omitting some access to catalog?

Thanks



-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

DrakoRod <drakoflames@hotmail.com> writes:
> I've a big problem with a database, is a PostgreSQL 9.6 version on Ubuntu.
> When a tried read some tables (approximately 7 of 1073) show this error: 

> *ERROR: cache lookup failed for function 125940*

> So, I was reading this like data corruption specially the postgresql's
> system catalog . I tried generate a dump but in any case, when I try make a
> database dump or table's dump show this error: 

> *pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
> function 406

Hmm, perhaps reindexing pg_proc would get you out of trouble?

Also, if that isn't 9.6.10, I'd suggest updating --- we have fixed
some data corruption issues since 9.6.0.

            regards, tom lane

Hi Tom

I reindex the pg_proc table and reindex finished correctly but I try read
the tables or make the dump and same error, reindexed the database and show
me this error:

ERROR:  cache lookup failed for function 125999

Any suggestions, If I run pg_upgrade to 10, will these errors be corrected?

Thanks!



-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

DrakoRod <drakoflames@hotmail.com> writes:
> I reindex the pg_proc table and reindex finished correctly but I try read
> the tables or make the dump and same error, reindexed the database and show
> me this error: 

> ERROR:  cache lookup failed for function 125999

This suggests you've actually lost some entries from pg_proc.  Have
you had any system crashes or suchlike?

> Any suggestions, If I run pg_upgrade to 10, will these errors be corrected? 

No, pg_upgrade can't magically restore data that's not there.

            regards, tom lane

>This suggests you've actually lost some entries from pg_proc.  Have
>you had any system crashes or suchlike?

yes two crashes for power outages, this guys doesn't have dumps recent, only
to 2 days ago, I try recover the much data as possible.

>No, pg_upgrade can't magically restore data that's not there.

I had a little hope that this possibility will work, but I understand that
is impossible after 2 crashes like those.

Another option? to recover something?

Thanks!
DrakoRod



-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Hey Tom,

Had a small query here.
If we have streaming replication enabled, and if we have corruption like this due to power loss etc, 
is it safe to assume we failover to standby  and we should be good (atleast in most cases)
I wanted to understand if the system catalog is corrupted, for some reason, will it/will it not  stream replicate the
corrupteddata to standby.
 
Or they are a property of an individual database server and will be rebuilt/updated on pg_dump/restore.

I am sorry if I am diverting the Drako's query or if that is not related, I'll ask another question then __
But I guess maybe the answer may help Drako too.

Thanks,
Vijay

On 8/20/18, 12:53 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

    DrakoRod <drakoflames@hotmail.com> writes:
    > I reindex the pg_proc table and reindex finished correctly but I try read
    > the tables or make the dump and same error, reindexed the database and show
    > me this error: 
    
    > ERROR:  cache lookup failed for function 125999
    
    This suggests you've actually lost some entries from pg_proc.  Have
    you had any system crashes or suchlike?
    
    > Any suggestions, If I run pg_upgrade to 10, will these errors be corrected? 
    
    No, pg_upgrade can't magically restore data that's not there.
    
                regards, tom lane