Wrapping postgresql commands in a transaction: truncate vs delete or upsert / merge

Question

I use the following commands below in postgresql 9.1.3 to move data from a staging table to a table used by webapp (geoserver), all in the same db. Then cancel the pace table.

• TRUNCATE table_foo;

• INSERT INTO table_foo

  SELECT * FROM table_temp;

• TABLE DROP table_temp;

I want to wrap this in a transaction in order to allow concurrency. The data set is smaller than 2000 rows, and truncation is faster than deletion.

• What is the best way to run these commands in a transaction?
• Whether the function makes sense or writes UPSERT / MERGE, etc. in CTE?
• Would it be better to DELETE all rows and then the INSERT array from the temporary table instead of TRUNCATE?
• In postgres, which will allow TRUNCATE or DELETE rollback?
• The temp table is delivered daily through an ETL written in the spirit of how can I automate truncation / deletion / bulk insert parts in postgres?
• I am open to using PL / pgsql, PL / python (or the recommended py for postgres).

I am currently executing sql commands manually after the temp configuration table is imported into my DB.

Answer 1

Both truncate and delete can be dropped (as clearly described in the manual).

truncate - by nature - has some oddities about visibility.

See the manual for more details: http://www.postgresql.org/docs/current/static/sql-truncate.html (warning below)

If your application can live with the fact that table_foo is “empty” during this process, truncation is probably better (look at the big red box in the manual again for an explanation). If you do not want the application to notice, you need to use delete

To run these statements in a transaction, just put them in one:

 begin transaction; delete from table_foo; insert into .... drop table_temp; commit; 

Whether you do this in a function or not is up to you.

truncate / insert will be faster (than delete / insert ) as this will minimize the amount of WAL generated.