Re: Temporary tables under hot standby

On Thu, May 3, 2012 at 1:57 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Michael,
>
>> What is the use case for temporary tables on a hot standby server?
>>
>> Perhaps this is a noobie question, but it seems to me that a hot standby
>> server's use by* applications* or *users* should be limited to transactions
>> that don't alter the database in any form.
>
> A very common use for asynchronous replicas is to offload long-running
> reporting jobs onto the replica so that they don't bog down the master.
>  However, long-running reporting jobs often require temporary tables,
> especially if they use some 3rd-party vendor's reporting tool.  For
> example, the average Microstrategy report involves between 1 and 12
> temporary tables.

Many tools and applications choose to use temporary tables. Often this
isn't necessary at all, for example in MicroStrategy it is possible to
ask it to use derived tables instead and thus avoid using temp tables,
so that can still work against Hot Standby.

Derived tables means rewriting the query from
CREATE TEMP TABLE s1 AS <SELECT1>;
SELECT ... FROM s1 WHERE ...

into
SELECT ... FROM (<SELECT1>) AS s1 WHERE

Many apps are easily rewritten in this way and so the lack of temp
tables isn't a total blocker in the way some people think.

If we had Global Temp Tables, users would still need to rewrite their
code, just in a different way, like this...
(on master)
CREATE GLOBAL TEMP TABLE s1 (....);

(on standby)
INSERT INTO s1 <SELECT1>;
SELECT ... FROM s1 WHERE ...
which seems to me to be actually harder than just rewriting as derived
table and isn't an option on Microstrategy etc, hence my observation
that GTTs don't help HS much. What I would like to see, one day, is
for temp tables to work without any changes.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services