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

Question

I am using the following commands below in postgresql 9.1.3 to move data from a temp staging table to a table being used in a webapp (geoserver) all in the same db. Then dropping the temp table.

1. TRUNCATE table_foo;

2. INSERT INTO table_foo

   SELECT * FROM table_temp;

3. DROP TABLE table_temp;

I want to wrap this in a transaction to allow for concurrency. The data-set is small less than 2000 rows and truncating is faster than delete.

• What is the best way to run these commands in a transaction?
• Is creating a function advisable or writing a UPSERT/MERGE etc in a CTE?
• Would it be better to DELETE all rows then bulk INSERT from temp table instead of TRUNCATE?
• In postgres which would allow for a roll back TRUNCATE or DELETE?
• The temp table is delivered daily via an ETL scripted in arcpy how could I automate the truncate/delete/bulk insert parts within postgres?
• I am open to using PL/pgsql, PL/python (or the recommended py for postgres)

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

Nevernever

Answer 1

Both, truncate and delete can be rolled back (which is clearly documented in the manual).

truncate - due to its nature - has some oddities regarding the visibility.

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

If your application can live with the fact that table_foo is "empty" during that process, truncate is probably better (again see the big red box in the manual for an explanation). If you don't want the application to notice, you need to use delete

To run these statements in a transaction simply put them into one:

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

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

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

Largo

Thanks for the suggestions. This worked out great. I'm going with DELETE FROM vs TRUNCATE due to being MVCC safe. Do you have any suggestions or experience with using python for calling psql commands. I am using pyodbc and placing the transaction in a python query string. Though I have heard about using psycopg2. – Nevernever

@EnzovsJacques: sorry, I don't use Python. – Largo