Message-ID: <6481440a-d974-4e35-b2a5-79a13c4b79d2@vondra.me>
Date: Sun, 25 Aug 2024 17:53:56 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: ERROR: could not open relation with OID XXXX
To: Marcelo Zabani <mzabani@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: <CACgY3QYe3mqMkODOzwx=EN4iGoddObUotLL8hGnCg==3vmxd_g@mail.gmail.com>
 <73bc7752-442c-4c67-a263-46017f1948cc@vondra.me>
 <CACgY3QYdsYyZQR0hDYFLrk42OJcNOkbW4nfmrauN7byad=6f0A@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <CACgY3QYdsYyZQR0hDYFLrk42OJcNOkbW4nfmrauN7byad=6f0A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/6481440a-d974-4e35-b2a5-79a13c4b79d2%40vondra.me>
Precedence: bulk


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