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