ERROR: cache lookup failed for function 125940

[DrakoRod]

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

Re: ERROR: cache lookup failed for function 125940

[DrakoRod]

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

Re: ERROR: cache lookup failed for function 125940

[DrakoRod]

>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

DELETE Query Hang

[DrakoRod]

Hi folks! 

I Have a question, in a database are a table with many files (bytea) stored
(I support this database a don't design it), but we need delete many rows
(38000 rows approx),  but I when execute query: 

BEGIN;
ALTER TABLE my_file_table DISABLE TRIGGER ALL;
DELETE FROM my_file_table WHERE id_table <> 230;

This query hang... 50 minutes and the query do not finish. 

Any suggestion? 



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

Re: DELETE Query Hang

[DrakoRod]

I understand, yep is a 9.5 version, Can I use query like this to execute
smaller batches?:

DELETE FROM my_table
WHERE id IN (
SELECT id
FROM logtable
LIMIT 10
);

Thanks




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

Table Partitioning: Sequence jump issue 10 in 10 with serial datatype

[DrakoRod]

Hi folks!!
I have a problem with a serial data type and partitioned table, I used rules
to insert in child tables. But the problem is that the some does'nt insert
and the sequence value jump sometimes 3 in 3 or 10 in 10. 

The example is the next: 




I don't understand why sequence jumps in this case 4 in 4. And how to avoid
this? I really can't change the use serial data type and how generate the
id, because I don't developed the app.

If I use triggers this don't happen? 

Thanks!






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

Re: Table Partitioning: Sequence jump issue 10 in 10 with serial datatype

[DrakoRod]

Sorry, your right! The example is:

CREATE TABLE customers (
id serial PRIMARY KEY, 
name TEXT,
other_data TEXT
);

CREATE TABLE customers_part1(
 CHECK (id<1) 
)INHERITS (customers);

CREATE TABLE customers_part2(
 CHECK (id>=1 AND id<2) 
)INHERITS (customers);

CREATE OR REPLACE RULE inserts_customer_part1
AS ON INSERT TO customers
WHERE new.id < 1
DO INSTEAD  INSERT INTO customers_part1 SELECT NEW.*;

CREATE OR REPLACE RULE inserts_customer_part2
AS ON INSERT TO customers
WHERE new.id >= 1 AND new.id < 2
DO INSTEAD  INSERT INTO customers_part2 SELECT NEW.*;

INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 
INSERT INTO customers (name, other_data) VALUES ('XXx','YY'); 


dd=# SELECT * FROM customers; 
 id |  name   | other_data 
+-+
  3 | XXx | YY
  7 | XXx | YY
 11 | XXx | YY
 15 | XXx | YY
 19 | XXx | YY
 23 | XXx | YY
(6 rows)




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

Re: Table Partitioning: Sequence jump issue 10 in 10 with serial datatype

[DrakoRod]

Yep!!

Today I tested with triggers instead rules and the sequence goings well.

Thanks for your help!! 



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