Re: Temporary tables under hot standby

On Wed, Apr 25, 2012 at 11:08 AM, Nicolas Barbier
<nicolas(dot)barbier(at)gmail(dot)com> wrote:
> 2012/4/25 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>
>> So you are saying it is OK to not be able to *create* them on HS, just
>> *use* pre-defined tables?
>>
>> That's almost useless IMHO.
>>
>> Applications expect to be able to do this all in the same transaction
>> on one session
>> CREATE TEMP TABLE x;
>>  ...DML commands...
>> SELECT ... FROM x;
>
> That’s not how standard-like temporary tables work, they are supposed
> to be declared beforehand. That makes sense if you consider the schema
> and the set of database-using applications as one. I assume that
> wanting to define applications independently from the database schema
> is the reason of existence for the PG-like temporary transactions.
>
> The way standard-like temporary tables work is exactly why I assume
> Noah proposes to implement them: because they work nicely with HS.

Well, following a standard that no other major DBMS has followed is
not great, especially if it leads to a non-useful feature.

Many software products generate CREATE TEMP TABLE statements
dynamically. This design would prevent ALL of them from working, as
well as preventing all current programs from using temp tables in the
currently accepted way, so the whole concept is very regrettably
flawed.

I very much support Noah's work to "make temp tables work on hot
standby", but we must solve the main problem, not just implement "make
a completely new kind of temp table work on hot standby". I have no
objection to "make a new kind of temp table", but that does not solve
the "make temp tables work on hot standby" problem.

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