Strange error in Redshift stored procedure

Question

I have a stored procedure in Redshift that is loading data from a bunch of S3 JSON files in a loop. Every time a file is loaded, its name is stored into a log table on Redshift side so i can link it to records in my staging tables, which i also use to avoid loading same file twice. Any error during the loading is also recorded. Anyways, everything worked fine for some time, but during the last run, I got following error (this is the exact error message):

catalog is missing 1 attribute(s) for relid 5477788

Checking AWS documentation for Redshift, and looking into tables STL_ERROR, STL_LOAD_ERRORS, SVL_STORED_PROC_MESSAGES, SVL_STORED_PROC_CALL, pg_class did not clarify anything. Any googling points only to PostgreSQL mentioning possible database corruption. I know that Redshift is based on PostgreSQL, but, how can i check it specifically for Redshift, that my database or table(s) are not corrupted? Has anyone encountered similar error in Redshift before?

Answer 1

I think, I figured it out.

The stored procedure I mentioned in the OP loops over a list of files in S3 and loads them to a temp table, enriches with some additional data such as file name etc., and then loads to a permanent staging table.

So, this temp table was created via CREATE TABLE ... AS ... LIMIT 0 inside a loop thag goes through a list of files. The temp table was then dropped as a part of iteration in the end.

With a high # of iterations and multiple SP calls, sooner or later, this error happened again. The reason why i could not find any data for the relid from the error was that it was relid of the temp table in fact.

I think, it is due to Redshift specifics that some of the nodes maybe could not catch up such a cycle of multiple temp table creation and dropping in a short period of time.

Once I moved out temp table creation outside the loop, and only truncated it at the end of each iteration, I have not seen the error again.