5

I'm running a command to create a new column on a fairly large table (about 315 million rows). This used more disk space than I expected and it has completely filled up my SAN. The command failed with the ERROR: could not extend file "pg_tblspc/14342 etc.": No space left on device

My questions are, when a command fails like this, are the results of the command not immediately discarded? Why when no actual changes have been made to the table, is the tablespace still full?

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?

Improve this question
3
  • When a logical change (e.g. DML) fails, the results are always rolled back. But there is no rollback mechanism for physical changes. If they fail they can always end up partially completed.
    – Andrew Brennan
    Jul 5, 2016 at 11:23
  • Thanks for the comment @Andrew. Looking at the column that would have been populated by the command, it contains no data. So the results of the command were not partially committed to the table.
    – Matt
    Jul 5, 2016 at 13:10
  • Yes that's correct behaviour. I'll add a full explanation in my answer below.
    – Andrew Brennan
    Jul 5, 2016 at 13:54

1 Answer 1

Reset to default
6

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

Improve this answer
2
  • Thanks for the answer @Andrew... interesting. However, this has happened a few times to me over the last couple of days. But it was only that first time where it got stuck with the hard drive virtually full. Multiple other commands have failed since then for the same reason (no space left on device), and the space used has been returned. Any thoughts?
    – Matt
    Jul 7, 2016 at 14:00
  • If the space looks returned but it still says there is no space, it might be held by some open cursors. You might need to remount the file system. The easiest way to do that is to reboot the SAN.
    – Andrew Brennan
    Jul 11, 2016 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.