Re: could not open relation with OID

On Wed, Jan 26, 2022 at 05:30:01PM -0800, Ben Chobot wrote:
> We do a lot of queries per day, over a lot of hosts, all of which are on
> 12.9. We've recently started doing a better job at analyzing our db logs and
> have found that, a few times a day, every day, we see some of our queries
> fail with errors like:
>
> could not open relation with OID 201940279
>
> In the cases we've examined so far, the failed query succeeds just fine when
> we run it manually. The failed query also had run on an async streaming
> replica, and the primary has completed at least one autovacuum since the
> failure. I don't know if either of those two facts are relevant, but I'm not
> sure what else to blame. The internet seems to want to blame issues like
> this on temp tables, which makes sense, but in our case, most of the queries
> that are failing this way are simple PK scans, which then fall back to the
> table to pull all the columns. The tables themselves are small in row count
> - although some values are likely TOASTed - so I would be surprised if
> anything is spilling to disk for sorting, which might have counted as a temp
> table enough to give such an error.

Do those OIDs point to some specific relations? It should be easy
enough to guess to which pg_class entry they point to, especially if
you have a persistent schema, and it these are indeed temporary
entries or not depending on their pg_class.relnamespace.

> This is a minuscule failure percentage, so replicating it is going to be
> hard, but it is still breaking for reasons I don't understand, and so I'd
> like to fix it. Has anybody else seen this, or have an ideas of what to look
> at?

I don't recall seeing such reports recently.

> Other things we've considered:
>     - we run pg_repack, which certainly seems like it could make an error
> like this, but we see this error in places and times that pg_repack isn't
> currently running

It could also take time for the issue to show up, depending on the
state of the relcache.
--
Michael