Skip site navigation (1) Skip section navigation (2)

Re: Temporary tables under hot standby

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Noah Misch <noah(at)leadboat(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Temporary tables under hot standby
Date: 2012-05-02 15:14:27
Message-ID: 20120502151427.GH25122@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

In response to

Responses

pgsql-hackers by date

Next:From: Peter GeogheganDate: 2012-05-02 15:35:52
Subject: Latch for the WAL writer - further reducing idle wake-ups.
Previous:From: Robert HaasDate: 2012-05-02 15:10:18
Subject: clog double-dip in heap_hot_search_buffer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group