Re: Unloading a table consistently

Christophe <xof(at)thebuild(dot)com> writes:
> I will have a log table which, once a day or so, is copied to a file
> (for movement to a data warehouse), and the log table emptied. For
> performance, the log table on the production system has no indexes,
> and is write-only. (The unload process is the only reader.)

> To unload it, I will be doing:

> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> COPY log TO 'filename-path';
> TRUNCATE log;
> COMMIT;

> My understanding is that I need the SERIALIZABLE isolation level so
> that the COPY and TRUNCATE see exactly the same view of the table.

No, the SERIALIZABLE bit is useless if not actually counterproductive
here. What you'll actually need is an exclusive lock:

BEGIN;
LOCK TABLE log;
COPY log TO 'filename-path';
TRUNCATE log;
COMMIT;

to prevent anyone from inserting new rows during the COPY. Otherwise
any such rows would be lost at the TRUNCATE.

You might be able to get away with a lock that only locks out writers
and not readers (offhand I think IN SHARE MODE is the right thing),
but from your description that would be a useless refinement anyway.

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

Here, the serializable mode is exactly what you want to guarantee
that the DELETE deletes the same rows the COPY copied. This is
a great deal less efficient than TRUNCATE, but it's secure for
concurrent insertions, which TRUNCATE is definitely not.

regards, tom lane