I am getting
"ERROR: out of shared memory"
presumably because my plpgsql function drops/creates a lot of temporary tables.
Searching for a solution to the issue, I found this answer that says:
So I guess one answer is that you will need enough locks to cope with all these tables being added/dropped throughout the transaction. Alternatively, you could try to reuse the temp tables between queries, simply truncate them to remove all the temp data
In order to avoid having too many looks, I am thinking about rewriting as many temporary table code using Common Table Expressions (CTEs).
My questions are:
Do the table expressions in CTEs use locks that count towards
Will replacing temp tables with CTEs solve the