Re: DELETE or TRUNCATE?

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(at)teksol(dot)info>wrote:

> 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