Re: Unloading a table consistently

In article <9478(dot)1209833817(at)sss(dot)pgh(dot)pa(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

% If you can't tolerate locking out writers for that long, you won't
% be able to use TRUNCATE. The operation I think you were imagining is
%
% BEGIN;
% SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
% COPY log TO 'filename-path';
% DELETE FROM log;
% COMMIT;
% VACUUM log; -- highly recommended at this point

How about something along the lines of

BEGIN;
ALTER TABLE log RENAME to log_old;
CREATE TABLE log(...);
COMMIT;

BEGIN;
LOCK table log_old;
COPY log_old TO 'filename-path';
DROP TABLE log_old;
COMMIT;

I believe this will keep the writers writing while keeping the efficiency
of truncating.
--

Patrick TJ McPhee
North York Canada
ptjm(at)interlog(dot)com