4

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?

3
  • 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.
    – dezso
    Oct 14, 2016 at 8:56
  • @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
    – Andremoniy
    Oct 14, 2016 at 9:37
  • 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.
    – dezso
    Oct 14, 2016 at 9:39

1 Answer 1

2

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).

3
  • How much work it will be to rewrite those functions?
    – dezso
    Oct 14, 2016 at 11:54
  • 2-4 hours. I've already did it :) Look, I have no choice.
    – Andremoniy
    Oct 14, 2016 at 11:59
  • 1
    So you didn't have hundreds of them, that's good news :D
    – dezso
    Oct 14, 2016 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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