Patrick TJ McPhee wrote:
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.
It's almost a pity that there's no
TRUNCATE TABLE log MOVE DATA TO log_copy;
or similar; ie with two identical table definitions `log' and `log_copy'
swap the backing file from `log' to `log_copy' before truncating `log'.
`log_copy' could be a new temp table created with CREATE TEMPORARY TABLE
... LIKE.
This sort of thing doesn't seem to come up all that much, though.
--
Craig Ringer
