I have been speaking with Amazon support about this but their only solution is to "keep trying," which has not been successful.
The issue is when running our ETL we intermittently get the following error when trying to drop a table:
InternalError: cache lookup failed for relation SOME_ID
Amazon say the following:
The problem is on the Postgres DB engine caching (Redshift is based on postgres 8.0.2) . This issue is recorded on our SIM and is something to do with the System Catalog Cache access which we inherit from Postgres v 8.0.2, the issue gets reproduced when DROP is used very often and the cache is not able to retrieve data which seems to be out-of-sync. After sometime (few seconds) the case is in back in sync and query runs fine. The only workaround I see for the moment is to adjust the logic and retry query when fail, or pause execution of the query between DROP statements.
Has anyone else come across this? I have tried repeatedly executing the drop but it always fails.
