Re: xlogdump fixups and WAL log question.

On Sat, 2006-10-21 at 15:17 -0400, Theo Schlossnagle wrote:
> On Oct 21, 2006, at 3:12 PM, Simon Riggs wrote:
> 
> > On Sat, 2006-10-21 at 09:00 -0400, Theo Schlossnagle wrote:
> >> On Oct 21, 2006, at 6:08 AM, Martijn van Oosterhout wrote:
> >>
> >>> On Sat, Oct 21, 2006 at 10:37:51AM +0100, Simon Riggs wrote:
> >>>> Turning off WAL is a difficult topic. Without it you have no crash
> >>>> recovery, which IMHO everybody says they don't care about until  
> >>>> they
> >>>> crash, then they realise. It's hard to be selective about  
> >>>> writing WAL
> >>>> for specific operations also.
> >>>
> >>> It's been discussed before. One idea is to declare tables without
> >>> logging. The idea being that during recovery those tables and  
> >>> related
> >>> indexes are simply truncated. No foreign keys allowed. Obviously  
> >>> they
> >>> will not be saved via PITR either.
> >>>
> >>> Put another way, the table structure is saved in WAL, but the data
> >>> isn't.
> >>
> >> This is exactly what I'd like.  Simon suggested turning off WAL
> >> during the loads as a possible hack solution.  The reason this won't
> >> work is that we snap all the time, lots of tables.  We have between
> >> 2000 and 4000 snapshot operations per day (throughout).  At the same
> >> time we have reporting queries running (that create and/or populate
> >> other tables) that last from 5 minutes to 18 hours.  It is important
> >> that we run everything but the snapshots with WAL on (as we must have
> >> PITR -- sans snapshots)
> >
> > These tables are loaded once then read-only, yes?
> 
> No, they are loaded, and then reloaded, and then reloaded. Queries  
> that use them will get the most recently loaded version of them.  It  
> meets a business rule like: table foo on the warehouse should be  
> representative of version of table foo on OLTP no older than 30 minutes.

But they can be re-created anew with the same name each time? Or I guess
not, but you redefine a view every 30 minutes to point to the latest
one?

If so, then I have a patch that will speed up COPY when in the same
transaction as the table that created it. I've finally fixed a bug in my
earlier prototypes that seems to make that work now, in all cases.

I was being slightly slow before; I thought this was a new requirement
but its just the old one slightly restated.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com