Re: Temporary tables under hot standby

On Wed, Apr 25, 2012 at 5:46 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> I don't know how GTT play inside the Oracle stack such that they
> aren't super popular, but if they work in the standby they will
> quickly become a killer feature.  IMNSHO it's annoying but acceptable
> to be forced to define them into the permanent schema.  Lack of temp
> tables on the standby is a popular question/complaint on irc and in
> most cases the proposal would satisfactorily address the problem.
>

The problem with using GTT for this is, IMHO, that you need to know
what your temp table will look before hand.

I have seen applications that uses the same name (ie: temp1, t1, tt or
t_temp) for all or almost all temp tables and, of course, all those
have different structures.

I have seen also temp tables created dinamically based in a query
(which has more or less columns based on some criteria).

In any case, this means for being able to use GTT on HS for these
applications, the apps needs to be fixed to ensure all temp tables
have different names through the app, also you need to ensure that all
queries that create temp tables to have a fixed set of columns.

Finally, you will need to modify apps to remove all CREATE TEMP TABLE
because they already exists. And i have not mentioned the problem i
will have if i need different behaviour for ON COMMIT (oh! i just did)

so yes, you can workaround things to make this something usable to fix
the problem of "temp tables in HS" but is not transparent (unless you
come from oracle, most db's uses local temp tables just as postgres
does) and certainly is not an ideal solution... FWIW, no one that i
know will want to do those "fixes" in their app.

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación