X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 860D79DC82F for ; Fri, 24 Mar 2006 06:07:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65947-09 for ; Fri, 24 Mar 2006 06:07:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 549039DC94D for ; Fri, 24 Mar 2006 06:07:29 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id EA46F56437; Fri, 24 Mar 2006 04:07:31 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Fri, 24 Mar 2006 04:07:31 -0600 Date: Fri, 24 Mar 2006 04:07:31 -0600 From: "Jim C. Nasby" To: Qingqing Zhou Cc: pgsql-performance@postgresql.org Subject: Re: WAL logging of SELECT ... INTO command Message-ID: <20060324100731.GO90527@pervasive.com> References: <20060321122211.GU15742@pervasive.com> <1142973230.24487.501.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060324:zhouqq@cs.toronto.edu::sQLl5JybO2TSaxCz:0000000000000000 0000000000000000000000002oAE X-Hashcash: 1:20:060324:pgsql-performance@postgresql.org::Bi52puXzpPYbH7mG:00000 00000000000000000000000005mn X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200603/481 X-Sequence-Number: 17918 On Wed, Mar 22, 2006 at 02:20:39PM +0800, Qingqing Zhou wrote: > > "Simon Riggs" wrote > > On Tue, 2006-03-21 at 06:22 -0600, Jim C. Nasby wrote: > > > Currently, it appears that SELECT * INTO new_table FROM old_table logs > > > each page as it's written to WAL. Is this actually needed? Couldn't the > > > database simply log that the SELECT ... INTO statement was executed > > > instead? Doing so would likely result in a large performance improvement > > > in most installs. Is there no provision for writing anything but data > > > page changes (or whole pages) to WAL? > > > > AFAIK it takes the same code path as CREATE TABLE AS SELECT, which > > already does exactly what you suggest (except when using PITR). > > > > As I read, they did take the same code path, but did they "simply log that > the SELECT ... INTO statement was executed"? If so, how can we rely on the > unreliable content of the old_table to do recovery? Why would the content of the old_table be unreliable? If we've replayed logs up to the point of the CTAS then any data that would be visible to the CTAS should be fine, no? Though, the way Tom put it in one of his replies it sounds like WAL doesn't do any kind of statement logging, only data logging. If that's the case I'm not sure that the CTAS would actually get replayed. But I suspect I'm just misunderstanding... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461