Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases?
I tried an experiment where I used the Foreign Data Wrapper and executed SQL like I had above. In between the insert and delete lines I put a sleep and then killed the process while it was sleeping. No data was committed as a consequence of the insert until it actually hit the connection.commit() line.
So, to answer my own question, it should be safe.