Re: Unloading a table consistently

In article <9478.1209833817@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.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@interlog.com