Re: Temporary tables under hot standby

On Thu, Apr 26, 2012 at 08:56:40AM +0100, Simon Riggs wrote:
> 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 under hot standby.
> 3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values
>   delay pg_clog truncation and can trigger a wraparound-prevention shutdown.
> 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 permanent tables.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.

I think if implementing global temporary tables only for hot standby
user (#7), it might be of limited usefulness, but the ability to avoid
system table churn (#1) means global temporary tables would have a wide
usefulness, even without hot standby use.  The idea of sharing optimizer
statistics also has a lot of merit.  

FYI, global temp tables have been on the TODO list long before EDB
employment or PostgresXC.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +