[GENERAL] DELETE or TRUNCATE?

[François Beausoleil]

Hi!

I have a process that replaces the contents of a table. The canonical data 
store is somewhere else. At the moment, the import looks like this:

CREATE TEMPORARY TABLE markets_import( LIKE x INCLUDING ALL );
COPY markets_import FROM STDIN;
...
\.
-- COPY a bunch of other tables

BEGIN;

TRUNCATE markets;
INSERT INTO markets SELECT * FROM markets_import;
-- do the other tables here as well

COMMIT;

VACUUM ANALYZE markets;

Sometimes, the import process will block on the TRUNCATE because some other 
query already holds a ShareLock on the markets table, because it's doing a long 
report. I'm guessing TRUNCATE prevents the use of MVCC, and DELETE would be 
better in this case? Especially since I'm doing a VACUUM ANALYZE at the end 
anyway.

Thanks!
François

smime.p7s
Description: S/MIME cryptographic signature