Обсуждение: could not open relation with OID
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.
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?
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
- although all our servers are currently on 12.9, I don't think this is a new error for us. I believe we might have seen it on previous minor versions of 12 and probably on 9.5 as well.
- our filesystem is xfs and seems reliable. I would expect that if it was a filesystem level error, it would not be so transient. We do, occasionally, expand our filesystems, but not at all the times we've seen this error.
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.
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?
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
- although all our servers are currently on 12.9, I don't think this is a new error for us. I believe we might have seen it on previous minor versions of 12 and probably on 9.5 as well.
- our filesystem is xfs and seems reliable. I would expect that if it was a filesystem level error, it would not be so transient. We do, occasionally, expand our filesystems, but not at all the times we've seen this error.
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
Вложения
Michael Paquier wrote on 1/26/22 9:14 PM:
So.... tell me more about stale relcaches? It turns out I was totally wrong and this is being driven by pg_repack. I can even make it happen pretty easily:
1. Put some data in a table with a single btree index on a primary db.
2. Set up streaming replication to a secondary db.
3. In a loop on the primary, have pg_repack repack the indices of that table. (the -x flag)
4. In a loop on the secondary, have psql query the secondary db for an indexed value of that table.
When I do this with replication, I can get the OID error consistently within 30 minutes. Without replication, I've been unable to get it to happen after 2 hours.
Given that this fails much faster on the secondary than the primary (where it has yet to fail at all) I'm leaning towards a postgres bug, but I'm happy to do more research to point the blame at something pg_repack is doing, if you could point me at a thing to research.
On Wed, Jan 26, 2022 at 05:30:01PM -0800, Ben Chobot wrote: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 runningIt could also take time for the issue to show up, depending on the state of the relcache.
So.... tell me more about stale relcaches? It turns out I was totally wrong and this is being driven by pg_repack. I can even make it happen pretty easily:
1. Put some data in a table with a single btree index on a primary db.
2. Set up streaming replication to a secondary db.
3. In a loop on the primary, have pg_repack repack the indices of that table. (the -x flag)
4. In a loop on the secondary, have psql query the secondary db for an indexed value of that table.
When I do this with replication, I can get the OID error consistently within 30 minutes. Without replication, I've been unable to get it to happen after 2 hours.
Given that this fails much faster on the secondary than the primary (where it has yet to fail at all) I'm leaning towards a postgres bug, but I'm happy to do more research to point the blame at something pg_repack is doing, if you could point me at a thing to research.