Re: Unloading a table consistently

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 TEM****ARY TABLE 
.... LIKE.

This sort of thing doesn't seem to come up all that much, though.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general