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

Re: WAL logging of SELECT ... INTO command

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: WAL logging of SELECT ... INTO command
Date: 2006-03-24 10:07:31
Message-ID: 20060324100731.GO90527@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-patchespgsql-performance
On Wed, Mar 22, 2006 at 02:20:39PM +0800, Qingqing Zhou wrote:
> 
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> 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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-03-24 10:14:40
Subject: Re: Scaling up PostgreSQL in Multiple CPU / Dual Core
Previous:From: Jim C. NasbyDate: 2006-03-24 10:05:14
Subject: Re: WAL logging of SELECT ... INTO command

pgsql-patches by date

Next:From: Simon RiggsDate: 2006-03-24 12:32:10
Subject: Re: WIP: splitting BLCKSZ
Previous:From: Jim C. NasbyDate: 2006-03-24 10:05:14
Subject: Re: WAL logging of SELECT ... INTO command

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