0

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?

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.