DELETE or TRUNCATE?
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
Attachments:
Hi,
Yes,DELETE would be better this case.
The TRUNCATE operation required AccessExclusiveLock on Table before perform
TRUNCATE operation.
So,if you the table size is bing,it is batter to do ANALYZE <Table> after
report and VACUUM <table> non-peak(less business) hours.
Regards,
Chiru
On Thu, May 16, 2013 at 7:52 PM, François Beausoleil
<francois@teksol.info>wrote:
Show quoted text
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 tablesBEGIN;
TRUNCATE markets;
INSERT INTO markets SELECT * FROM markets_import;
-- do the other tables here as wellCOMMIT;
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