On Oct 21, 2006, at 4:40 PM, Simon Riggs wrote:
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?
closest to the latter. A view is redefined when the new snapshot is
complete.
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.
We don't use COPY. We directly INSERT INTO target_snap SELECT * from
remote_select(...) t(cast);
remote_select is part of dbi-link.
// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
