Re: ERROR: could not open relation with OID XXXX

On 8/25/24 17:36, Marcelo Zabani wrote:
>> we do some special stuff for catalogs
> 
> That is good to know, thanks!
> 
>> I believe you could actually lock the pg_class rows for update. Just
> add FOR UPDATE at the end of the query.
> 
> Thanks, but I tried that and got "ERROR:  permission denied for table
> pg_class", even if I try it only for tables the user owns.
> 

Yeah, I tried that with a superuser, I didn't realize it'd fail for
regular users who don't have rights to modify pg_class directly.

> At least considering the use-case of avoiding this error due to
> temporary tables/indexes (which are a part of normal application
> execution), I was thinking of using materialized CTEs that filters those
> out, and only after that using other functions that for example take
> OIDs and return definitions. Other kinds of DDL that create
> non-temporary tables can be "blamed" on developers in my case.
> Do you think using those materialized CTEs could help? And do you think
> this can be considered a bug that I should report or is it just too
> edge-casey to consider?

I have no idea what you mean - what would be filtered out, etc. But you
can give it a try - you have a reproducer, so testing if it fails should
not be that difficult.

Other than that, I think you can do two things:

1) Make the application to simply retry in it sees this error.

2) Lock the objects using LOCK TABLE before running the query.


regards

-- 
Tomas Vondra