Postgres command failed: No space left on device
when a command fails like this, are the results of the command not immediately discarded?


You need to understand the difference between the logical and physical structures of the database. When you insert 350m rows to a table and it fails, the logical changes are rolled back.

But when the underlying datafiles have to be increased in size to accomodate the 350m rows, this physical change is not rolled back.


  Why when no actual changes have been made to the table, is the tablespace still full?


While your 350m rows were being added, the tablespace grew its datafiles to accomodate the new data. When the data was deleted, the space remains. Deletions will not shrink the datafile, it will merely mark the space as unused and leave it there to be reused later. The space will only be returned to the operating system if the DBA issues a VACUUM FULL command.


  And finally, what is the best way to free up space again when postgres will not allow to me execute any other commands e.g. VACUUM?


Multiple valid strategies for fixing this problem are provided in these two older questions:

I need to run VACUUM FULL with no available disk space

VACUUM returning disk space to operating system