Re: Temporary tables under hot standby - Mailing list pgsql-hackers

On Wed, Apr 25, 2012 at 7:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 25, 2012 at 1:31 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> However, it is a fallacy that this is a good solution for using temp
>> tables on HS. I think the wish to enhance Oracle compatibility is
>> making some wishful thinking happen with regard to how useful this is
>> going to be. We need to spend just as much time considering the
>> utility of our work as we do spending time on the quality of the
>> implementation, otherwise its just well-implemented shelfware.
>
> Well, like I say, if you want to use locally-defined temp tables on
> HS, you have to somehow solve the problem of catalog entries, and
> nothing in your email looks like a proposal for how to do that.  I've
> come up with one design, which I sketched in my original response, but
> it relies on creating some new system catalogs that are themselves
> GTTs, and it's also hideously complicated.  If you or anyone else can
> come up with a better design, great, but so far no one has.

Previous discussions had Tom proposing ways of extending catalogs to
allow exactly this. So designs that address that point are already on
record.

A full GTT implementation is not required and the design differed from
that. I don't think "hideously complicated" is accurate, that's just
you're way of saying "and I disagree". Either route is pretty complex
and not much to choose between them, apart from the usefulness of the
end product - GTTs are not that beneficial as a feature in themselves.

The current problems of our temp table approach are
1. Catalog bloat
2. Consumption of permanent XIDs for DML on temp tables.  This increases COMMIT cost on the master and is a non-starter
underhot standby. 
3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values delay pg_clog truncation and can trigger a
wraparound-preventionshutdown. 
4. sinval traffic from every CREATE TEMP TABLE et al.
5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.
6. We don't automatically drop temporary tables that existed at the point of a crash, because they look much like
permanenttables.2. Cross-backend 
access/security
7. Temp tables don't work on HS
8. No Global Temp tables

Implementing GTTs solves (8) and provides some useful tools to solve
other points. Note that GTTs do not themselves solve 1-7 in full,
hence my point that GTTs are an endpoint not a way station. The way
forwards is not to concentrate on GTTs but to provide a set of
facilities that allow all the more basic points 1-6 to be addressed,
in full and then solve (7) and (8).  If we pretend (8) solves (7) as
well, we will make mistakes in implementation that will waste time and
deliver reduced value.

In passing I note that GTTs are required to allow PostgresXC to
support temp tables, since they need a mechanism to makes a single
temp table definition work on multiple nodes with different data in
each.

Simply put, I don't think we should be emphasising things that are
needed for PostgresXC and EDB AS, but not that important for
PostgreSQL users.

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