Re: WAL logging of SELECT ... INTO command

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