Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1F9FA64FE39
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  6 Nov 2008 17:50:08 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 53307-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  6 Nov 2008 17:50:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 7F63D64FD2E
	for <pgsql-performance@postgresql.org>;
	Thu,  6 Nov 2008 17:50:07 -0400 (AST)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Thu, 06 Nov 2008 15:50:05 -0600
Message-Id: <49131226.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.3 
Date: Thu, 06 Nov 2008 15:49:59 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Scott Carey" <scott@richrelevance.com>
Cc: <jd@commandprompt.com>,<pgsql-performance@postgresql.org>
Subject: Re: Create and drop temp table in 8.3.4
References: <4911957A.EE98.0025.0@wicourts.gov>
	<4911D4DB.EE98.0025.0@wicourts.gov> <4912EAFF.EE98.0025.0@wicourts.gov>
	<1225999071.1204.14.camel@jd-laptop.pragmaticzealot.org>
	<4912F29E.EE98.0025.0@wicourts.gov>
	<a1ec7d000811061305n7b28800od469c1411e1a9928@mail.gmail.com>
In-Reply-To: <a1ec7d000811061305n7b28800od469c1411e1a9928@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Archive-Number: 200811/38
X-Sequence-Number: 31769

>>> "Scott Carey" <scott@richrelevance.com> wrote: 
> Note that Write Barriers can be very important for data integrity
when power
> loss or hardware failure are a concern.  Only disable them if you
know the
> consequences are mitigated by other factors (such as a BBU + db using
the
> WAL log with sync writes), or if you accept the additional risk to
data
> loss.
 
For those using xfs, this link may be useful:
 
http://oss.sgi.com/projects/xfs/faq.html#wcache
 
> On Temp Tables:
> I am a bit ignorant on the temp table relationship to file creation
-- it
> makes no sense to me at all that a file would even be created for a
temp
> table unless it spills out of RAM or is committed.  Inside of a
transaction,
> shouldn't they be purely in-memory if there is space?  Is there any
way to
> prevent the file creation?  This seems like a total waste of time for
many
> temp table use cases, and explains why they were so slow in some
exploratory
> testing we did a few months ago.
 
As I learned today, creating a temporary table in PostgreSQL can
easily create four files and do dozens of updates to system tables;
that's all before you start actually inserting any data into the
temporary table.
 
-Kevin