Create temp tables in Amazon RDS PostgreSQL read-replica

Question

I have dozens of legacy stored procedures which create temporary tables inside for collecting results for a read-only application.

I've created a read-replica of my PostgreSQL in Amazon RDS and tried to perform this procedures, but failed, as it doesn't allow to create even temporary tables in a read-only transaction.

Are any ways how to solve this issue with minimal efforts?

• András Váczi: Move those queries to the master. PostgreSQL currently does not allow creating temporary tables on read-only replicas, this is not an RDS specific issue.
• Andremoniy: @dezso Great advice, read please my question more carefully: these procedures are used for reading data and I can not change DB type, only read-replica
• András Váczi: Eh, you are right, as the way of maintaining a replica is different in RDS than in plain PostgreSQL, it is an RDS specific issue, with its effect being very similar in both cases :D Unfortunately for you, read replicas are also read-only, so no temp tables there.

Answer 1

As the most easy way which I found to solve this issue - is wrapping all "temporary tables" into separate stored procedures, which return TABLE(...) type, and then use Common Table Expressions (https://www.postgresql.org/docs/9.1/static/queries-with.html).

• András Váczi: How much work it will be to rewrite those functions?
• Andremoniy: 2-4 hours. I've already did it :) Look, I have no choice.
• András Váczi: So you didn't have hundreds of them, that's good news :D