Обсуждение: ERROR: XX000: cache lookup failed for type 75083631
Hi there,
DROP VIEW access_group_view;
ERROR: XX000: cache lookup failed for type 75083631
LOCATION: format_type_internal, format_type.c:152
I'm having an issue with dropping a view as shown below:
ERROR: XX000: cache lookup failed for type 75083631
LOCATION: format_type_internal, format_type.c:152
I've also checked the source code here, which :
I've tried to clear the cache as mentioned in some other thread on community using option with number 3 from here: https://www.cyberithub.com/drop-flush-clear-cache-memory-ram-in-linux/
-> Didn't help
We are running postgresql in a docker container.
PostgreSQL 9.6.12 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
This is an old instance, but I would like to figure out what caused it going forward and ideally how to fix it. We've upgraded in the latest version to 13.1 also planning to upgrade to 14.2 in a few days.
Ideas or what info to provide?
Jan Beseda <besedajohn@gmail.com> writes:
> I'm having an issue with dropping a view as shown below:
> DROP VIEW access_group_view;
> ERROR: XX000: cache lookup failed for type 75083631
> LOCATION: format_type_internal, format_type.c:152
Does the behavior change if you say CASCADE?
The fact that it's failing in format_type() implies that something is
trying to print the name of a type, which doesn't seem like a main-line
activity for DROP VIEW. I am suspicious that pg_depend shows this type
OID as dependent for some reason on this view, and that the message
that it was trying to print was complaining about how that dependency
existed and that you'd need to say CASCADE to make it take. However,
since format_type() fails, the type OID must not really exist anymore,
implying that the pg_depend entry is orphaned.
That raises a different set of questions about how it got to be that way.
But at any rate, what I'd suggest is
1. Verify that the type OID is wrong:
select * from pg_type where oid = 75083631;
If that finds a row then we've got a whole other set of issues.
(BTW, if you want to be really sure, forcing a seqscan for this
query or reindexing pg_type could be advisable.)
2. Check for bogus entries in pg_depend:
select * from pg_depend where objid = 75083631;
select * from pg_depend where refobjid = 75083631;
3. If there's just one hit in pg_depend then it's probably
safe to delete that row.
regards, tom lane
Hi Tom, Thanks for getting me directions for debugging, but it seems the devops team fully restored the system snapshot on corrupted instance for me. If it occurs again I'll reopen/write you as reply here if that's ok.
Cheers, Jan
čt 14. 4. 2022 v 19:24 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Jan Beseda <besedajohn@gmail.com> writes:
> I'm having an issue with dropping a view as shown below:
> DROP VIEW access_group_view;
> ERROR: XX000: cache lookup failed for type 75083631
> LOCATION: format_type_internal, format_type.c:152
Does the behavior change if you say CASCADE?
The fact that it's failing in format_type() implies that something is
trying to print the name of a type, which doesn't seem like a main-line
activity for DROP VIEW. I am suspicious that pg_depend shows this type
OID as dependent for some reason on this view, and that the message
that it was trying to print was complaining about how that dependency
existed and that you'd need to say CASCADE to make it take. However,
since format_type() fails, the type OID must not really exist anymore,
implying that the pg_depend entry is orphaned.
That raises a different set of questions about how it got to be that way.
But at any rate, what I'd suggest is
1. Verify that the type OID is wrong:
select * from pg_type where oid = 75083631;
If that finds a row then we've got a whole other set of issues.
(BTW, if you want to be really sure, forcing a seqscan for this
query or reindexing pg_type could be advisable.)
2. Check for bogus entries in pg_depend:
select * from pg_depend where objid = 75083631;
select * from pg_depend where refobjid = 75083631;
3. If there's just one hit in pg_depend then it's probably
safe to delete that row.
regards, tom lane